Remove Duplicate record from a table using CTE

In this blog we will discuss how to remove duplicate record from a table using CTE. Without using TOP, MAX. There is a table name Employee and three columns like EmpId, EmpName, Salary.

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

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

Delete from delrecord where mydel > 1