how to delete duplicate record in tables ?

Question

I have a emp table.It`s have 1000 records.

Empid       Name       Department
1            AA        IT 
2            AB        SALES 
3            AA        IT
1            AA        IT 
2            AB        SALES 
4            CC        MR
1            AA        IT


I want to delete duplicate record.

Table take only one record of every person

Output of record like
Empid       Name       Department
1            AA        IT 
2            AB        SALES 
3            AA        IT
4            CC        MR

Pankaj Pandey · Answer

DELETE FROM table WHERE table .%%physloc%% NOT IN ( SELECT MIN(b.%%physloc%%) FROM table b GROUP BY b. Empid  b.NAMEb.Department);

Javeed M Shaikh · Answer

try this on dev table first not on production: WITH list_duplicates (empid name deptduplicate_count) AS ( SELECT empid name dept ROW_NUMBER() OVER(PARTITION BY empid name dept ORDER BY empid name dept) AS duplicate_count FROM tablename ) DELETE FROM list_duplicates WHERE duplicate_count > 1