Delete Duplicate Data from SQL Table Using Common Table Expression (CTE)

DECLARE @DuplicateRcordTable TABLE (Col1 varchar(10), Col2 varchar(10)) INSERT INTO @DuplicateRcordTable SELECT 'A', 'B'
UNION ALL SELECT 'A', 'B' --duplicate
UNION ALL SELECT 'C', 'C' --duplicate
UNION ALL SELECT 'C', 'C'
UNION ALL SELECT 'C', 'C' --duplicate
UNION ALL SELECT 'D', 'E'
UNION ALL SELECT 'E', 'F'

SELECT * FROM @DuplicateRcordTable;

WITH CTES(COl1,Col2, DuplicateCount)
 AS
 (
            SELECT COl1,Col2, ROW_NUMBER()
              OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount
           FROM @DuplicateRcordTable
 )
DELETE FROM CTES WHERE DuplicateCount > 1

SELECT * FROM @DuplicateRcordTable