Tip: Removing duplicate rows from a table in SQL Server by leaving the last inserted one as it is

By Anil Kumar Reddy Perugu, published on 21 Oct 2011 Category: SQL SERVER 2005, 2008

In order to delete or make duplicate rows inactive by leaving the latest one as it is

1) First need to identify the unique constraint or fields for unique combination.

2) Identify column like CreatedOn or Primary Key (Unique identity value), based on which we can identify the original record to leave

3) Apply ROW_NUMBER() with Partition by group of fields that forms unique combination and ordered by CreatedOn or Primary Key (Unique identity value).

4) Delete the rows with row number/rank greater than 1

Example:

CREATE TABLE TestTable(PKID int, col1 int, col2 int, col3 char(50))
insert into TestTable values (1, 1, 1, 'one')
insert into TestTable values (2, 1, 1, 'one')
insert into TestTable values (3, 1, 2, 'two')
insert into TestTable values (4, 1, 2, 'two')
insert into TestTable values (5, 1, 2, 'two')
insert into TestTable values (6, 1, 3, 'three')
insert into TestTable values (7, 1, 1, 'one')

In the above example col1, col2,col3 needs to be unique for rows. Hence need to remove duplicates leaving latest records as it is.

;WITH duplicates AS
(
    SELECT PKID, ROW_NUMBER() OVER (PARTITION BY col1, col2, col3 order by PKID DESC) AS R_NO
    FROM TestTable 
)
DELETE FROM t
FROM TestTable t 
INNER JOIN duplicates d on d.PKID = t.PKID
    AND d.R_NO > 1

The above delete query will remove first 4 records from the table leaving last three unique rows as it is.