How do I find duplicates across multiple columns?
Duplicated id
for pairs name
and city
:
select s.id, t.* from [stuff] sjoin ( select name, city, count(*) as qty from [stuff] group by name, city having count(*) > 1) t on s.name = t.name and s.city = t.city
SELECT name, city, count(*) as qty FROM stuff GROUP BY name, city HAVING count(*)> 1
Something like this will do the trick. Don't know about performance, so do make some tests.
select id, name, cityfrom [stuff] swhere1 < (select count(*) from [stuff] i where i.city = s.city and i.name = s.name)