Deleting Exact Duplicates

Question

I have around 2000 exact duplicates in table and I want to delete one of them is there a way: The sample data looks like:

Year    ID   Name   Type
2015    200  John   S
2015    200  John   S 
2014    100  Peter  T
2014    100  Peter  T

and I want output as :

Year    ID   Name   Type
2015    200  John   S
2014    100  Peter  T

I have tried using Row_number but not sure how to delete

Answer 1

I'm going to assume you want to KEEP one row, not DELETE one row. It also wasn't clear if you only wanted to target this specific set of values or handle all sets of duplicates.

;WITH x(y,i,n,t,r) AS 
(
  SELECT [Year], ID, Name, [Type],
    ROW_NUMBER() OVER (PARTITION BY [Year], ID, Name, [Type] ORDER BY [Year])
  FROM dbo.table_name
  /*
    WHERE [Year] = 2015
    AND ID = 200
    AND Name = 'John'
    AND [Type] = 'S'
  */
)
DELETE x WHERE r > 1;

If you want to delete one row and keep 1999 duplicates, then change the last line to:

DELETE x WHERE r = 1;

If you want to only handle this set, remove the /* and */.

I also recommend staying away from bad column names like year and type (they require square brackets in a lot of cases) and ID (what does that mean?).