kdarty
7/2/2014 - 2:58 PM

Delete Duplicate Records in SQL Server Keeping only One

Delete Duplicate Records in SQL Server Keeping only One

--Description: Delete Duplicate Records in SQL Server Keeping only One
--Author: Ben Thul
--Source: http://stackoverflow.com/questions/6025367/t-sql-deleting-all-duplicate-rows-but-keeping-one
--NOTE: A common use-case is that "foo, bar" are the group identifier and "baz" is some sort of time stamp. In order to keep the latest, you'd do ORDER BY baz desc)

WITH cte AS (
  SELECT[foo], [bar], 
     row_number() OVER(PARTITION BY foo, bar ORDER BY baz) AS [rn]
  FROM TABLE
)
DELETE cte WHERE [rn] > 1