How to find duplicate rows and delete duplicate rows using SQL

How to find the duplicates?

Table: Emp

ID | NAME    

|  1 | Neva
|  1 | Neva       
|  2 | Jis    

Ah.. why Neva repeating in the table. 

Method 1: Group By

select ID, count(*)

  from   Emp

  group  by ID

  having count(*) > 1; 

 But the output only shows one row for each copy.

If you want to display  all the rows,query the table again and filter it by checking where the rows are in the results of the above query.

  select * from Emp where (ID, mame) in (select ID, name from   Emp group  by ID  having count(*) > 1); 

For large tables, this could take a long time.

Method 2: Analytics Functions using inline view

First method: 

select * from ( select e.*, count(*) over (partition by ID, name) count from Emp e) where count>1;

Second Method: 

with empcount as (select e.*, count(*) over (partition by ID, name)count from Emp e) select * from empcount where count>1;

How to delete the duplicate rows

1. Using rowid

delete from Emp where rowid not in (select max(rowid) from Emp group by ID);

Group by operation should be on the columns which identify the duplicates.

2. Using self-join

delete from Emp e1 where rowid not in (select max(rowid) from Emp e2 where e1.ID = e2.ID);

3. Using row_number()

delete from Emp where rowid in (select rid from(select rowid rid, row_number() over(partition by ID order by ID) rownum from emp) where rownum > 1);

4. Using dense_rank()

delete from Emp where rowid in(select rid from(select rowid rid,
dense_rank() over(partition by ID order by rowid) rownum from Emp) where rownum > 1);

Can use rank() and dens_rank() since both will give unique records when order by rowid.

5. Using Group By

delete from Emp where ID in(select ID from Emp group by ID having count(*) >1)

delete from Emp where(ID,name) in(select max(ID),name from Emp group by name);

More Examples 

Query 1

SET ROWCOUNT 1 
DELETE FROM Emp WHERE ID = 1 

SET ROWCOUNT 0  

Query 2

DELETE TOP(1) FROM EMPLOYEE WHERE EMPNO = 1;

Query 3

DELETE TOP (SELECT COUNT(*) -1 FROM EMPLOYEE WHERE EMPNO = 1)  
FROM EMPLOYEE  
WHERE EMPNO = 1;

All the above three query removes duplicates