Delete Duplicate Rows from SQL Server Table

This example is about deleting duplicate rows in SQL server table. In this example duplicate records are deleted from the table based on rank.

/*Create temp table*/

create table #tmp(id int,name1 varchar(20),cre_date datetime)

GO

/*Insert rows with duplicate data in it*/

insert into #tmp values(1,'name1','2011-03-30 9:00 am')
insert into #tmp values(1,'name1','2011-03-30 9:20 am')
insert into #tmp values(2,'name2','2011-03-30 9:30 am')
insert into #tmp values(3,'name3','2011-03-30 9:40 am')
insert into #tmp values(3,'name3','2011-03-30 9:50 am')
insert into #tmp values(3,'name3','2011-03-30 9:55 am')

GO

/*Delete the rows that are duplicate*/

with t as (
Select id,name1,row_number() over (partition by id,name1 order by cre_date) rnk from #tmp )
delete from t where rnk>1