sqlsql-serversql-delete

Delete duplicate max row in SQL server

I want to delete duplicate rows in SQL. My query can delete duplicate rows for RN > 1, but I want to delete max(RN) instead:

WITH CTE AS(
   SELECT [col1], [col2], [col3]
       RN = ROW_NUMBER()OVER(PARTITION BY [col1], [col2] ORDER BY [col1], [col2])
   FROM dbo.Table1
)
DELETE FROM CTE WHERE RN > 1

Example data

Solution

  • Just change the partition to the reverse of the current order, then delete just records with the first row number:

    WITH CTE AS (
    SELECT [col1], [col2], [col3]
           RN = ROW_NUMBER() OVER (PARTITION BY [col1], [col2] ORDER BY [col1] DESC, [col2] DESC)
    FROM dbo.Table1
)

DELETE FROM CTE WHERE RN = 1;