Remove Duplicate record from a table using CTE :-

This article 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.

Adv 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 remove 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