How To Delete Duplicate Records in SQL Oracle

 select rowid,empno,ename,job,mgr,hiredate,sal,comm,deptno from emp;

Using self-join with ROWID
=========================

delete FROM emp a
WHERE rowid NOT IN
  (SELECT max(rowid) FROM emp b WHERE a.empno = b.empno );
 
(OR)

select rowid,empno,ename FROM emp a
WHERE rowid NOT IN
  (SELECT min(rowid) FROM emp b WHERE a.empno = b.empno );
 
 rollback;
 
Using rowid with GROUP BY Clause
================================

delete FROM emp
WHERE rowid not  IN   (SELECT min(rowid) FROM emp GROUP BY empno);

(OR)

select * FROM emp
WHERE rowid NOT IN   (SELECT MAX(rowid) FROM emp GROUP BY empno);

rollback;


Using rowid with  row_number()
===============================
select * FROM emp
WHERE rowid IN
  (SELECT delrowid  FROM
    (SELECT rowid delrowid,row_number() over(partition BY empno order by empno) rownm
    FROM emp    )
  WHERE rownm > 1  );



Using rowid with dense_rank()
=============================
delete  FROM emp
WHERE rowid IN  (SELECT delrowid
  FROM
    (SELECT rowid delrowid, dense_rank() over(partition BY empno order by rowid) rownm
    FROM emp    )
  WHERE rownm > 1  );

rollback;

Using Group By
===============

select * FROM emp
WHERE (empno,ename,sal) IN
  (SELECT empno,ename,sal FROM emp GROUP BY empno,ename,sal having count(empno)>1 );
 
rollback;