Interview Answer

Deleting duplicate rows from a table

Go Back

Deleting duplicate rows from a table

												 desc HR.employees;
												 
												Name           Null     Type         
												-------------- -------- ------------ 
												EMPLOYEE_ID    NOT NULL NUMBER(6)    
												FIRST_NAME              VARCHAR2(20) 
												LAST_NAME      NOT NULL VARCHAR2(25) 
												EMAIL          NOT NULL VARCHAR2(25) 
												PHONE_NUMBER            VARCHAR2(20) 
												HIRE_DATE      NOT NULL DATE         
												JOB_ID         NOT NULL VARCHAR2(10) 
												SALARY                  NUMBER(8,2)  
												COMMISSION_PCT          NUMBER(2,2)  
												MANAGER_ID              NUMBER(6)    
												DEPARTMENT_ID           NUMBER(4)  

												

The following query delete the duplicate rows from the table.

Solution

   					 
												delete from hr.employees where rowid in
												(
												select rid from
												(
												select rowid rid,
												dense_rank() over(partition by employee_id order by rowid) rn
												from hr.employees
												)
												where rn > 1
												);