Delete Duplicate Records

This logic was found on PL/SQL Pipeline:
To test, insert a duplicate 'SMITH' on the EMP TABLE:

Insert Into Emp Values(9999,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
Commit;

You can see what is happening in the above report, and how a record is being chosen for deletion by running the following query. Records are being ranked (and a RowId saved) by breaking all the employees into partitions based on their name, hire date, and department. Within each 'partition,' they are ordered by the Rowid.

Select Rowid, Empno, EName, HireDate, Deptno,
               Rank() Over (Partition By EName, HireDate, DeptNo 
                                Order By Rowid) 
                 As
         Ranking
  From Emp;

This sets us up the delete:

Delete From Emp 
 Where Rowid In
      ( Select Rowid 
          From
               ( Select Rowid, 
                              Rank() Over (Partition By EName, HireDate, DeptNo
                                               Order By Rowid) 
                                As
                        Ranking
                 From Emp
               )
         Where Ranking > 1
      );