SQL SERVER Ranking Functions - RANK, DENSE_RANK, NTILE, ROW_NUMBER SQL query to delete duplicate rows SQL Server CTE(Common Table Expression) and Recursive Queries CTE Recursive query for data hierarchy(Parent Child hierarchy) Different methods of SQL queries to insert data in tables SQL query to display all columns with datatypes for a given Table name SQL query to check two tables have identical data SQL query to search a string in database Schema SQL query to display total number of rows for each table in database SQL query to delete duplicate rows

Create a table EmpDtl1 with some duplicate rows as shown below to to understand different methods of delete duplicate rows
create table EmpDup(empid int,name varchar(20))

insert into EmpDup values(1,'Andy')
insert into EmpDup values (1,'Andy')
insert into EmpDup values(2,'Bill')
insert into EmpDup values(2,'Bill')
insert into EmpDup values (2,'Bill')
insert into EmpDup values (3,'Chris')
User Name "Andy" repeated 2 times and User Name "Bill" repeated 3 times 
Following are the different methods for deleting duplicate rows

Method 1:
Insert the distinct rows from the duplicate rows table to new temporary table.
Delete data from table which has duplicate rows then insert the distinct rows from the temporary table as shown below.
select distinct * into #tmp From EmpDup
delete from EmpDup
insert into EmpDup                
select * from #tmp drop table #tmp
Method 2:
If you want to consider only few columns in a table for duplication criteria to delete rows then Method 1 will not work(in our example, EMDup table has more than 2 columns and delete rows if empid and name repeats more than one time).

Add a identity column as a serial number that acts as a row unique identier(auto incremental ascending order).
Then get the Rank against each empid,name. If Rank is greater than 1 means it's a duplicated row and delete the same. After deleting the duplicated rows then remove the identity column which is used for rank..
alter table EmpDup add  sno int identity(1,1)

delete E
from  EmpDup E
inner join
(select *,
RANK() OVER ( PARTITION BY empid,name ORDER BY sno DESC )rank
From EmpDup )T on E.sno=t.sno
where T.Rank>1

alter table EmpDup 
drop  column sno
If you feel this query is little difficult to understand then use following same query but in different way :)
alter table EmpDup add  sno int identity(1,1)
delete from EmpDup where sno in
(
select sno from (
select *,
RANK() OVER ( PARTITION BY empid,name ORDER BY sno DESC )rank
From EmpDup
)T
where rank>1
)

alter table EmpDup 
drop  column sno
If your sql server version is below  SQL2005 then above queries(above 2 queries in Method2) won't work since Rank() command is not available in SQL 2000 and below version.

If your sql server version is below SQL2005 then use below query. Same logic to get the rank but without using the Rank() command :)
alter table EmpDup add  sno int identity(1,1)
delete from EmpDup where sno in
(
select sno  from EmpDup D where 
1<(select count(*) from EmpDup A where A.empid=D.empid and A.name=D.name and D.sno>=A.sno)
)

alter table EmpDup 
drop  column sno
Method 3: 
Using "Delete Top( )" clause:

If you want to delete duplicate rows for a pirticular empid then use "Top()" command in delete query as shown below
delete top(2) From EmpDup where empid=2
OR:
delete top(select count(*)-1 From EmpDup x where x.empid=2) From EmpDup where empid=2
Method 4:
If you want to delete all the rows if the selected columns repeated more than 1 time then use following query, 
Query to delete these 3 duplicated rows or repeated more than 1 time,
delete from EmpDup where                
EmpID in(select EmpID from EmpDup group by EmpId having count(*) >1)
Posted by