Remove Duplicate record from a table using CTE

Before writing query using CTE first we learn what is CTE

Common Table Expression are temporary result sets which is defined within the execution scope of a single select , insert ,update, delete or createview statement.

Advantage of CTE 

1. Can be used to create a recursive query.
2. Can be substituted for a view.
3. Can reference itself multiple times..
4. Improve readability.
5. Easy maintenance of complex query.

Query for removing record

Select * , Row_Number() over(partition by EmpId, EmpName, Salary order by Name desc) as mydel  From Emplyee)

Delete from delrecord where mydel > 1