Interview Question of the Week #014 – How to DELETE Duplicate Rows

This is another interview question I hear often: how would you delete duplicate rows when the table has an identity column? I have to look up the exact syntax myself sometimes. The important part is deciding which row is the duplicate before writing DELETE.

Question: How do you delete duplicate rows in SQL Server when the table has an identity column?

Answer: First name the columns that define a duplicate. In my original example they were DuplicateColumn1, DuplicateColumn2, and DuplicateColumn3. Keep the row with the largest non-NULL identity ID in each group, and inspect the rows that would be removed:

SELECT ID, DuplicateColumn1, DuplicateColumn2, DuplicateColumn3
FROM dbo.MyTable
WHERE ID NOT IN
(
    SELECT MAX(ID)
    FROM dbo.MyTable
    GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3
);

If that result is exactly the set of unwanted rows, the original deletion pattern is:

DELETE FROM dbo.MyTable
WHERE ID NOT IN
(
    SELECT MAX(ID)
    FROM dbo.MyTable
    GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3
);

Use a transaction and check the preview and affected row count before committing a change to real data. This example assumes ID is a non-NULL, unique identity. The largest ID is a chosen survivor, not proof that it is the most recently created or most correct row. Check foreign keys and the actual business rule too: matching values can represent legitimate separate events.

When the survivor rule is more complicated, ROW_NUMBER() lets you partition by the duplicate columns and order by the row you want to keep. Keep the original answer simple for this particular interview question, then explain why the rule must be explicit.

I also recorded a short video showing this approach.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.