Way to delete the duplicate records in oracle

Suppose we have the table EMPLOYEE with following table structure

Now in the column EMPLOYEE_FULL_NAME we have duplicate records for JOHN & BRUCE

The query for deleting the duplicate records is below:


DELETE FROM EMPLOYEE A
WHERE
ROWID >
(SELECT MIN(ROWID) FROM EMPLOYEE B
WHERE
b.EMPLOYEE_FULL_NAME = a.EMPLOYEE_FULL_NAME
 );

This query should delete the records which are duplicate for column EMPLOYEE_FULL_NAME.

 Note:
If there are more columns to be included in the duplicate list, then please add those columns near the self join statement , like

DELETE FROM EMPLOYEE A
WHERE
ROWID >
(SELECT MIN(ROWID) FROM EMPLOYEE B
WHERE
b.EMPLOYEE_FULL_NAME = a.EMPLOYEE_FULL_NAME
AND
B.FIRST_NAME = A.FIRST_NAME
     ); 


Hope this makes it clear to delete the duplicate rows.

There are many other ways also, but this one is simplest one.


The other way is:

DELETE FROM EMPLOYEE
WHERE  rowid IN
       ( SELECT LEAD(rowid) OVER
                (PARTITION BY EMPLOYEE_FULL_NAME ORDER BY NULL)
         FROM   EMPLOYEE );

Thanks
Ashish