How to delete duplicate rows in a table using CTE

The “ROW_NUMBER()” in the above query returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. The “ORDER BY” clause determines the order in which the ROW_NUMBER value is assigned to the rows in a partition. The “PARTITION BY” clause used here to divide the result set into partitions.

  SalesOrderNo    ItemNo     Qty     UnitPrice        ROW_NO

2                 3             1       25000.00          1

The new column ROW_NO shows row numbers of the duplicate rows. To delete those duplicate rows we can use below statement

 WITH CTE_Dup AS

 (

 SELECT*,

ROW_NUMBER()OVER (PARTITIONBY SalesOrderno, ItemNo ORDER BY SalesOrderno,ItemNo)

AS ROW_NO

from dbo.SalesOrderDetails

DELETEFROM CTE_Dup WHERE ROW_NO > 1;

Output:

3 rows deleted.

SELECT * FROM SalesOrderDetails;

SalesOrderNo    ItemNo     Qty     UnitPrice

        1                 2              1       12000.00

2                 3             1       25000.00