Easy SQL duplicate row detection using Common Tables Expressions

Here’s a simple way to identify and/or delete duplicate records from SQL Server where you can choose which columns are using to identify the record as a duplicate.

 
WITH CTE AS
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY productId1,productId2 ORDER BY productId1,productId2) AS RN
    FROM [#RelatedProducts]
)

DELETE FROM CTE WHERE RN<>1

What this is doing is creating a Common Table Expression and adding a row number column where the row matches the columns defined  in the partition. In doing that each defined ‘duplicate’ record has a incremental row number. You can then delete all those where the row number isn’t 1, which removes the duplicates.

Posted on by Joe in SQL Server

Add a Comment