Delete duplicate rows Sql

Question

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

In the first example, why should 2 and 3 be deleted? In the second example, why should any row be accepted? — Tim Biegeleisen
– Tim Biegeleisen, Commented Aug 4, 2020 at 10:24
the reason is that the gifts could be attributed but not consumed by the client. — Wael Arbi
– Wael Arbi, Commented Aug 4, 2020 at 10:28
I don't think you're understanding what the PARTITION BY does, as you appear to be partitioning, and ordering on every columns apart from quantity.The columns in the PARTITION BY don't need to be in the ORDER BY. The PARTITION BY is the distinct groups you want to create, and the ORDER BY the order you want those distinct groups in. — Thom A
– Thom A ♦, Commented Aug 4, 2020 at 10:29
i'm still learning the ROW_NUMBER() function and this is my first time using it. Yes this is the problem, thanks for the quick reply — Wael Arbi
– Wael Arbi, Commented Aug 4, 2020 at 10:35

Fahmi · Accepted Answer · 2020-08-04 10:27:54Z

1

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

answered Aug 4, 2020 at 10:27

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Delete duplicate rows Sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related