Cool SQL query that deletes the duplicates

Found this on Sql Server Central:

CREATE TABLE #new(ID INT NULL, KeyValue VARCHAR(2))
INSERT INTO #new(ID, KeyValue) VALUES (1,'aa')
INSERT INTO #new(ID, KeyValue) VALUES (2,'bb')
INSERT INTO #new(ID, KeyValue) VALUES (1,'aa')
INSERT INTO #new(ID, KeyValue) VALUES (1,'aa')

SELECT * FROM #new;


Now let's delete the duplicates. The semicolon at the beginning is recommended if you are not using it after every SQL sentence. This tells the parser to begin with new statement. Without it you will probably get a syntax error.

;WITH Numbered AS (SELECT RowNo=ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID), ID, KeyValue FROM #new) DELETE FROM Numbered where RowNo>1;

SELECT * FROM #new;


Cleanup...

DROP TABLE #new;