Delete duplicate rows using Row_number and CTE in SQL

                   We can use Row_Number to delete duplicate rows from a table, as we know Row_Number function allocates serial number based on a key column.

Consider a table named 'EmpDetails' which has data as below:

As seen in the above screen shot there are duplicate records for EmpId 1,2 and 3

By using CTE and Row_number function we can remove the duplicate records from the above table. The query to remove duplicate values is as follow:

Query:

With CTE as
(select *,ROW_NUMBER() over(partition by empid order by empid) as Row_num from dbo.EmpDetails)

delete from CTE where Row_num > 1

This query will delete the duplicate rows from the EmpDetails table

Now, if we see the data in the EmpDetails Table it will contain only unique values in EmpID column.

Select * from dbo.EmpDetails