How to delete duplicate rows in sql?

How to delete duplicate rows in sql?, someone asked me to explain?

Here I am using CTE(Common Table Expression) for Delete duplicate rows.

PARTITION BY : It devides the query result into partitions

Row_NUMBER : gives unique name for all duplicate rows ie., if one row record repeated for 3 times . it gives the index such as 1,2 and 3

Using this query we can delete all duplicate rows in a table

With EmployeeCTE AS
Select *, ROW_NUMBER() Over (Partition BY ID order by ID)as RowIndex
from tbl_Employees


--- Here we are going to delete all duplicate records except one record(original)

Delete from EmployeeCTE Where RowIndex > 1



Post your comments / questions