How to delete duplicate rows using CTE

 Duplicate records cane be deleted in several ways in SQL Server. Delete the duplicate records using common table expression is one of method.

Let's see the below example.

Employee Table:

ID	FirstName	LastName	Gender	Salary
1	Mark	Hastings	Male	60000
1	Mark	Hastings	Male	60000
2	Mary	Lambeth	Female	30000
2	Mary	Lambeth	Female	30000
3	Ben	Hoskins	Male	70000
3	Ben	Hoskins	Male	70000
3	Ben	Hoskins	Male	70000

SQL Code:

WITH EmployeeCTE AS
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) AS RowNumber
FROM Employee
)
DELETE FROM EmployeeCTE WHERE RowNumber > 1

Output:

ID	FirstName	LastName	Gender	Salary
1	Mark	Hastings	Male	60000
2	Mary	Lambeth	Female	30000
3	Ben	Hoskins	Male	70000