Wednesday, October 12, 2016

Deleting all duplicate rows but keeping one

How can I simply remove duplicate rows?

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