SQL - find duplicates records, but delete the oldest

Question

Hi!

I've been struggling with a query all day, just cant seem to find the logic..

I have a table:

CREATE TABLE [dbo].[mytable](
 [Id] [int],
 [id2] [int],
 [id3] [int],
 [id4] [int],
 [CreatedOn] [datetime] 

I have to find records where id2, id3 & id4 are the same and then delete the one with the lowest CreatedOn value. (Id is the primary key)

Any ideas how to achieve this?

Thanks,

Zoe

Answer 1

Try this:

;WITH CTE_Duplicates AS (
    SELECT [Id], [Id2], [Id3], [Id4], ROW_NUMBER() OVER(PARTITION BY [Id2], [Id3], [Id4] ORDER BY [CreatedOn] DESC) AS rn
    FROM [dbo].[mytable]
)

DELETE CTE_Duplicates WHERE rn > 1;

Answer 2

@Guoxiong 's answer will delete all but the newest row and leave only one row of each duplicate. If you want to just delete the oldest row, then you want

 ;WITH CTE_Duplicates AS (  
     SELECT [Id], [Id2], [Id3], [Id4], ROW_NUMBER() OVER(PARTITION BY [Id2], [Id3], [Id4] ORDER BY [CreatedOn] ASC) AS rn  
     FROM [dbo].[mytable]  
 )  
   
DELETE CTE_Duplicates WHERE rn = 1;  

Tom