How to use CTE to remove duplicates

How to use CTE to remove duplicates

--One use of Common table Expressions (CTEs) is to remove duplicates
--To see this lets create a table 'Employee'
CREATE table Employee  (name NVARCHAR(100), age INT, salary INT)

--insert some duplicate data into the 'Employee Table'.
INSERT  into Employee
VALUES ('Mark', 42, 120000), ('Susan', 29, 125000), ('Mark', 42, 120000)
--you could see there is a duplicate record in the table by querying the table
--select * from  Employee
--we could use CTE to remove this duplicate. 
WITH CTE_Employee(name, age, salary, rownumber) AS (
select name,   age,   salary,   row_number() OVER (
PARTITION BY name, age, salary ORDER BY name, age, salary
) AS rownumber from Employee
)

DELETE
FROM CTE_Employee
WHERE rownumber <> 1
--query the table to see if there are still duplicates
--select * from  Employee