Deleting Duplicates using CTE and ROW_NUMBER Ranking Function
Assume you have the following table called SampleTable:
One approach to deleting a duplicate is to first assign sequential numbers that get reset for each group of unique records. We can do this by using the ROW_NUMBER() function and specifying a PARTITION BY clause. The columns in the PARTITION BY should identify the unique records in your table. In the example below, it is enough to specify StudentID.
; with cte AS ( SELECT ROW_NUMBER() OVER (PARTITION BY StudentID ORDER BY StudentID) RowNum, * FROM SampleTable )
If we were to select from this CTE, this is what you will get. Notice RowNum = 1 is repeated for every new instance of StudentID.
To delete duplicates, you simply need to delete from the CTE where RowNum is greater than 1:
DELETE FROM cte WHERE RowNum > 1