Removing Duplicates using CTE

Monday, 31 August 2009

Here's a code snippet which removes the duplicate records,using CTE

WITH CTE ([col1], cnt)

AS

(

SELECT [col1],

ROW_NUMBER() OVER(PARTITION BY [col1] ORDER BY [col1]) AS cnt

FROM table1

)

DELETE

FROM CTE

WHERE cnt > 1