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

  1. with delrecord as (
  2. Select * , Row_Number() over(partition by EmpId, EmpName, Salary order by Name desc) as mydel From Emplyee)
  4. Delete from delrecord where mydel > 1