remove dups, quick and simple

this is a nice quick method for removing duplicates in a table.  It appears to let me include as many fields as i want to determine what a duplicate is.  

Create a common table expression, and select fields, and a row number over some or all fields.  If there is more than one row for that selection, the row number will be 1 plus 1++ for each duplicate.  remove those with row number greater than 1.  I THINK that the order by might allow us to select which one we want to remove, but right now i’m happy with removing ANY duplicates.

;WITH CTE AS 
(
SELECT *, ROW_NUMBER() OVER(
PARTITION
BY field1, field2 
ORDER BY [field1]

AS Rnum FROM table

DELETE FROM CTE WHERE Rnum <> 1
Advertisements