NimChimpsky NimChimpsky - 4 years ago 88
SQL Question

How do I find duplicates across multiple columns?

So I want to do something like this sql code below:

from stuff s
group by having count(where city and name are identical) > 1

To produce the following, (but ignore where only name or only city match, it has to be on both columns):

id name city
904834 jim London
904835 jim London
90145 Fred Paris
90132 Fred Paris
90133 Fred Paris

Answer Source

Duplicated id for pairs name and city:

select, t.* 
from [stuff] s
join (
    select name, city, count(*) as qty
    from [stuff]
    group by name, city
    having count(*) > 1
) t on = and =
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download