Removing Duplicates - only all but the most recently dated row

Question

Possible Duplicate: How can I find duplicate entries and delete the oldest ones in SQL? I have a database which has a few thousand duplicates due to a faulty update tool. I am able to identify the collections of items with duplicates, but need to delete only the oldest entries, not necessarily the lowest id. Test data looks like this, correct row has an * The articles with duplicate titles which do not have duplicate ruleids should be deleted except for the most recently created rows. (actual id column is a GUID so I cannot assume auto-increment) ``` Id Article id Rule Id Title Opened Date -- ---------- ------- ----- ----------- 1* 111 5 T1 2013-01-20 2 112 5 T1 2013-07-01 3* 113 6 T2 2013-07-01 4* 114 7 T2 2013-07-02 5 115 8 T3 2012-07-01 6 116 8 T3 2013-01-20 7* 117 8 T3 2013-01-21 ``` Table Schema: ``` CREATE TABLE [dbo].[test_ai]( [id] [int] NOT NULL, [ArticleId] [varchar](50) NOT NULL, [ruleid] [varchar](50) NULL, [Title] [nvarchar](max) NULL, [AuditData_WhenCreated] [datetime] N…

Accepted Answer

The typical model for deleting one row from a set (or from each group in a set) in SQL Server 2005+ is:

```
;WITH cte AS 
(
  SELECT col, rn = ROW_NUMBER() OVER 
    (PARTITION BY something ORDER BY something)
  FROM dbo.base_table
  WHERE ...
)
DELETE x WHERE rn = 1;
```

In your case this would be:

```
;WITH cte AS 
(
  SELECT id, ruleid, Title, rn = ROW_NUMBER() OVER 
  (
     PARTITION BY ruleid, Title  
     ORDER BY auditdata_whencreated DESC
  )
  FROM dbo.test_ai
)
DELETE cte 
  OUTPUT deleted.id
  WHERE rn > 1;
```

Results:

```
id
----
2
6
5
```

Removing Duplicates - only all but the most recently dated row

Solution

Problem

Related problems