Oracle SQL Delete Duplicate records from table

--------------------------------------------------------
--  DDL for Table EMP_DUP
--------------------------------------------------------

  CREATE TABLE EMP_DUP
   (    EMPNO NUMBER(10),
    ENAME VARCHAR2(20 BYTE),
    SAL NUMBER(10)
   );

Insert into EMP_DUP (EMPNO,ENAME,SAL) values (1,'John',200);
Insert into EMP_DUP (EMPNO,ENAME,SAL) values (1,'John',200);
Insert into EMP_DUP (EMPNO,ENAME,SAL) values (2,'Akilesh',300);
Insert into EMP_DUP (EMPNO,ENAME,SAL) values (2,'Akilesh',300);
Insert into EMP_DUP (EMPNO,ENAME,SAL) values (2,'Akilesh',300);
Insert into EMP_DUP (EMPNO,ENAME,SAL) values (3,'Helen',400);
Insert into EMP_DUP (EMPNO,ENAME,SAL) values (3,'Helen',400);
Insert into EMP_DUP (EMPNO,ENAME,SAL) values (4,'Sai Shree',500);
Insert into EMP_DUP (EMPNO,ENAME,SAL) values (5,'Taqueer',600);

Records In Table  

EMPNO	ENAME	SAL
1	John	200
1	John	200
2	Akilesh	300
2	Akilesh	300
2	Akilesh	300
3	Helen	400
3	Helen	400
4	Sai Shree	500
5	Taqueer	600

 Deleted Records

EMPNO	ENAME	SAL
1	John	200
2	Akilesh	300
2	Akilesh	300
3	Helen	400

  After Deleting Records

EMPNO	ENAME	SAL
1	John	200
2	Akilesh	300
3	Helen	400
4	Sai Shree	500
5	Taqueer	600

 1.     By Using Self Join

select *  from emp_dup e1

where rowid not in

(select max(rowid) from emp_dup e2

where e1.empno = e2.empno );

 

Delete  from emp_dup e1

where rowid not in

(select max(rowid) from emp_dup e2

where e1.empno = e2.empno );

 

2.     By Using row_number

select * from emp_dup where rowid in

(

select rid from

(

select rowid rid,

row_number() over(partition by empno order by empno) rn

from emp_dup

)

where rn > 1

);

 

delete from emp_dup where rowid in

(

select rid from

(

select rowid rid,

row_number() over(partition by empno order by empno) rn

from emp_dup

)

where rn > 1

);

3.     By Using Dense_Rank

select *  from emp_dup where rowid in

(

select rid from

(

select rowid rid,

dense_rank() over(partition by empno order by rowid) rn

from emp_dup

)

where rn > 1

);

 

delete from emp_dup where rowid in

(

select rid from

(

select rowid rid,

dense_rank() over(partition by empno order by rowid) rn

from emp_dup

)

where rn > 1

);

 

4.     By Using rowid

 

select *

 from emp_dup

where rowid not in

(select max(rowid) from emp_dup group by empno,ename,sal);

 

delete from emp_dup

where rowid not in

(select max(rowid) from emp_dup group by empno,ename,sal);