SQL Server Interview Question #99

How can ROW_NUMBER() be used to identify duplicate records?

Aggregate Functions, GROUP BY & Window Functions Mid-Level Intermediate

Detailed Explanation

ROW_NUMBER can partition rows by the columns that define business duplication and assign a sequence within each duplicate group. Rows with a row number greater than 1 are duplicate occurrences according to that definition.

Before deleting duplicates, the business key and retention rule must be verified carefully. A deterministic ORDER BY should decide which row is kept.

Code Example

WITH DuplicateEmails AS
(
    SELECT Id,
           Email,
           ROW_NUMBER() OVER
           (
               PARTITION BY Email
               ORDER BY Id
           ) AS rn
    FROM dbo.Customers
    WHERE Email IS NOT NULL
)
SELECT Id, Email
FROM DuplicateEmails
WHERE rn > 1;