Is there a way to delete duplicates from a table with a single column in SQL Server?-CodePudding

Is there a possible way to delete duplicate rows from a table like this without dropping the table and re-creating it with distinct rows?

DROP TABLE IF EXISTS #temp;

CREATE TABLE #temp (id INT);

INSERT INTO #temp
VALUES (1), (2), (2), (2), (3), (3);

CodePudding user response：

Using a CTE and ROW_NUMBER grouped by the specified column you are looking at can cleanly remove duplicate values, as ROW_NUMBER counts incrementally with duplicate values of the PARTITION BY aggregation. When a new grouping is found when ordered, it resets the ROW_NUMBER to 1 starting at that next record of different grouped values.

 WITH CTE AS (
 SELECT *,
 ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col1) AS RN
 FROM #temp
 )

 DELETE FROM CTE WHERE RN<>1