Skip to content
Advertisement

SQL Select Duplicate Values based on Specific Condition

I am asking for a solution where I can find duplicate entries in a table but one of those duplicate entries MUST contain a specific value otherwise it won’t come in the result.

For example, my table is like this:

ID Name Type
1 Hassan Commercial
2 Ahmed Personal
3 Jack Personal
4 Hassan Commercial
5 Jack Commercial
6 Charlie Commercial

Expected result:

ID Name Type
3 Jack Personal
5 Jack Commercial

See, I do not want Id No. 1 & 4 as they do not have ‘Personal’ Value in their ‘Type’ Column.

But, Id No. 3 & 5 match my requirement.

I have tried the following query:

SELECT ID
 , Name
 , Type FROM (
  SELECT ID, Name, Type,
         COUNT(*) OVER (PARTITION BY Name) AS cnt
  FROM THIS_TABLE) AS t
WHERE t.cnt > 1

   

Thanks in Advance

Advertisement

Answer

Sounds like what you want is to first get the names of people with multiple account types

select name from this_table group by name having count(distinct type)>1

then dump the table for records matching those names.

select * from this_table where name in (
  select name from this_table group by name having count(distinct type)>1
)
User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement