Finding duplicate rows in SQL Server

select o.orgName, oc.dupeCount, o.idfrom organizations oinner join (    SELECT orgName, COUNT(*) AS dupeCount    FROM organizations    GROUP BY orgName    HAVING COUNT(*) > 1) oc on o.orgName = oc.orgName

You can run the following query and find the duplicates with max(id) and delete those rows.

SELECT orgName, COUNT(*), Max(ID) AS dupes FROM organizations GROUP BY orgName HAVING (COUNT(*) > 1)

But you'll have to run this query a few times.

You can do it like this:

SELECT    o.id, o.orgName, d.intCountFROM (     SELECT orgName, COUNT(*) as intCount     FROM organizations     GROUP BY orgName     HAVING COUNT(*) > 1) AS d    INNER JOIN organizations o ON o.orgName = d.orgName

If you want to return just the records that can be deleted (leaving one of each), you can use:

SELECT    id, orgNameFROM (     SELECT          orgName, id,         ROW_NUMBER() OVER (PARTITION BY orgName ORDER BY id) AS intRow     FROM organizations) AS dWHERE intRow != 1

Edit: SQL Server 2000 doesn't have the ROW_NUMBER() function. Instead, you can use:

SELECT    o.id, o.orgName, d.intCountFROM (     SELECT orgName, COUNT(*) as intCount, MIN(id) AS minId     FROM organizations     GROUP BY orgName     HAVING COUNT(*) > 1) AS d    INNER JOIN organizations o ON o.orgName = d.orgNameWHERE d.minId != o.id