How To Delete Duplicate Records in SQL Oracle

drop table emp_dup;

create table emp_dup( 

empno number, 

ename varchar2(50), 

job varchar2(50), 

sal number, 

deptno NUMBER);

insert into emp_dup values(101,'KING','PRESIDENT',1000,10);

insert into emp_dup values(102,'CLARK','MANAGER', 800,20);

insert into emp_dup values(103,'FORD','MANAGER',750,10);

insert into emp_dup values(102,'CLARK','MANAGER', 800,20);

insert into emp_dup values(104,'JAMES','MANAGER',820,30);

insert into emp_dup values(105,'WARD','SALESMAN',500,10);

insert into emp_dup values(104,'JAMES','MANAGER',820,30);

insert into emp_dup values(106,'FORD','SALESMAN',400,10);

insert into emp_dup values(104,'JAMES','MANAGER',820,30);

insert into emp_dup values(107,'SMITH','SALESMAN',450,10);

commit;

  Solution 1:Using rowid with GROUP BY Clause

 delete from emp_dup  

 where rowid not in (

 select max(rowid) from emp_dup  

 group by EMPNO, ENAME, JOB, SAL, DEPTNO);

 

delete from emp_dup 

where rowid not in (

select min(rowid) from emp_dup  

group by EMPNO, ENAME, JOB, SAL, DEPTNO);

   

        Solution 2:Using rowid with Self Join

Delete  from emp_dup a

 where rowid > ( select min(rowid) from emp_dup b 

 where a.empno = b.empno and a.ename = b.ename and a.job=b.job and a.sal= b.sal and a.deptno = b.deptno);      

 

 Solution 3:Using rowid with Row_number       

delete from emp_dup where rowid in (    

select rowid from (

select rowid, row_number() over(partition by EMPNO, ENAME, JOB, SAL, DEPTNO order by rowid) r

from emp_dup)

where r >1);

 

      Solution 4:Using rowid with Rank

delete from emp_dup where rowid in (    

select rowid from (

select rowid, rank() over(partition by EMPNO, ENAME, JOB, SAL, DEPTNO order by rowid) r

from emp_dup)

where r >1);

 

 Solution 5:Using rowid with Dense_Rank

delete from emp_dup where rowid in (    

select rowid from (

select rowid, dense_rank() over(partition by EMPNO, ENAME, JOB, SAL, DEPTNO  

order by rowid) r 

 from emp_dup) where r >1);