CODEWITHSUNDEEP
sqlsql-server
Wael Arbi
Wael Arbi

Reputation: 27

Delete duplicate rows Sql

I should delete duplicate rows from my table. My table structure is : ClientId-GiftId-Invoice-Quantite

Example of Duplicate Rows :

Example 1:
 1. C1-G1-Inv1-1
 2. C1-G1-Inv1-0
 3. C1-G1-NULL-NULL

Expected Result : 2 AND 3 should be deleted

Example 2:

 1. C2-G1-NULL-NULL
 2. C2-G1-NULL-NULL
 3. C2-G1-NULL-NULL

Expected Result : any row is accepted

My delete query is :

WITH CTE AS(
   SELECT ClientID,GiftID,Invoice,Quantity,
       RN = ROW_NUMBER()OVER(PARTITION BY ClientID,GiftID,Invoice,Quantity ORDER BY 
 ClientID,GiftID,Invoice)
   FROM #RowsToDelete
)
Delete FROM CTE where RN > 1

i'm new with use of ROW_NUMBER() Function and i think i didn't figure how to use it properly Thanks in advance

Upvotes: 0

Views: 78

Answers (1)

Fahmi
Fahmi

Reputation: 37493

You need to change your partition by and order by clause like below -

WITH CTE AS(
   SELECT ClientID,GiftID,Invoice,Quantity,
       RN = ROW_NUMBER() OVER(PARTITION BY ClientID,GiftID ORDER BY Quantite desc)
   FROM #RowsToDelete
)
Delete FROM CTE where RN > 1

Upvotes: 1

Related Questions