SQL Server › Common Table Expressions (CTEs)

How would you use a CTE combined with a window function to identify and remove duplicate rows from a table?

Advanced

Answer

You define a CTE that uses ROW_NUMBER combined with a PARTITION BY clause to assign a sequential number to each duplicate group based on the columns that define a duplicate, and then you can delete or select only the rows where that row number is greater than one, effectively removing every duplicate except the first occurrence.

WITH DuplicateRows AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY Email ORDER BY CustomerId) AS RowNum
  FROM Customers
)
DELETE FROM DuplicateRows WHERE RowNum > 1;

Real-world example A marketing database cleans up duplicate customer records that share the same email address, using a CTE with ROW_NUMBER to keep only the earliest record for each customer and remove the rest.

Common follow-ups: How do you choose which duplicate row to keep when there are several to choose from?;Can this same pattern be used to identify duplicates without actually deleting them?

Window Functions;Data Types & Schema Design