Best way to Remove Duplicate Records with help of CTE in sql

Nice method to avoid using temp/derived and group by operation in removing duplicate records.


COMMON TABLE EXPRESSION(CTE)

DROP TABLE DuplicateRcordTableCREATE TABLE DuplicateRcordTable (Col1 INT, Col2 INT)INSERT INTO DuplicateRcordTableSELECT 1, 1UNION ALLSELECT 1, 1 --duplicateUNION ALLSELECT 1, 1 --duplicateUNION ALLSELECT 1, 2UNION ALLSELECT 1, 2 --duplicateUNION ALLSELECT 1, 3UNION ALLSELECT 1, 4SELECT * FROM DuplicateRcordTableWITH CTE (COl1,Col2, DuplicateCount)AS(SELECT COl1,Col2,ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount DuplicateRcordTable)DELETE
FROM CTEWHERE
GO

Thanks

Puneet Kumar

DuplicateCount > 1

FROM