Shark Shark - 1 year ago 31
SQL Question

Get duplicate on single column after distinct across multiple columns in SQL

I have a table that looks like this:

name | id
-----------
A 1
A 1
B 2
C 1
D 3
D 3
F 2


I want to return id's 1 and 2 because they are duplicate on names. I don't want to return 3, because it is distinct for D 3.

Basically, I'm thinking of doing a query to first get a distinct pairing, so the above reduces to

name | id
-----------
A 1
B 2
C 1
D 3
F 2


And then doing a duplicate find on the id column. However, I'm struggling to find the correct syntax to construct that query.

Answer Source

You should be able to get the result you want by using a GROUP BY along with a HAVING clause that counts the distinct names. The HAVING clause will filter for those ids that have more than one distinct name:

select id
from Table1
group by id
having count(distinct name) > 1

Here is a demo

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download