Oracle - Use RANK to delete duplicate rows

Hi Everyone,

This is an example of the RANK function to identify and remove duplicate rows from Oracle tables, which deletes all duplicate rows while leaving the initial instance of the duplicate row.

DELETE FROM emp where ROWID IN

(SELECT ROWID FROM

(SELECT ROWID, rank_n FROM

(

SELECT 

RANK() OVER (PARTITION BY emp_id ORDER BY ROWID) rank_n, 

ROWID AS “ROWID" 

FROM 

emp 

WHERE emp_id IN (SELECT emp_id FROM emp GROUP BY emp_id, emp_name, desig HAVING COUNT(*) > 1)

)

)WHERE rank_n > 1

)

Method step-by-step

1. Finds the primary key (emp_id) of duplicate records

2. Rank the duplicate records (rank_n)

3. And then deletes all records other than with the rank 1.

Thanks for reading my blog !