xtine xtine - 2 years ago 77
SQL Question

Finding duplicate rows in SQL Server

I have a SQL Server database of organizations, and there are many duplicate rows. I want to run a select statement to grab all of these and the amount of dupes, but also return the ids that are associated with each organization.

A statement like:

SELECT orgName, COUNT(*) AS dupes
FROM organizations
GROUP BY orgName

Will return something like

orgName | dupes
ABC Corp | 7
Foo Federation | 5
Widget Company | 2

But I'd also like to grab the IDs of them. Is there any way to do this? Maybe like a

orgName | dupeCount | id
ABC Corp | 1 | 34
ABC Corp | 2 | 5
Widget Company | 1 | 10
Widget Company | 2 | 2

The reason being that there is also a separate table of users that link to these organizations, and I would like to unify them (therefore remove dupes so the users link to the same organization instead of dupe orgs). But I would like part manually so I don't screw anything up, but I would still need a statement returning the IDs of all the dupe orgs so I can go through the list of users.

Any help would be greatly appreciated. Thanks :)

Answer Source
select o.orgName, oc.dupeCount, o.id
from organizations o
inner join (
    SELECT orgName, COUNT(*) AS dupeCount
    FROM organizations
    GROUP BY orgName
    HAVING COUNT(*) > 1
) oc on o.orgName = oc.orgName
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download