DELETE Duplicates

Question

DELETE Duplicates

Golden_Eye

SSC-Addicted

Points: 463
More actions
September 16, 2013 at 8:40 am

#277302

Hello,
I did a check in my table and I found these duplicates:
Key Month Usage
45 Sept 464
45 Sept 471
28 Sept 762
28 Sept 766
41 Sept 561
41 Sept 568
47 Sept 862
47 Sept 872
How to delete the duplicate rows with less usage?
For example for
45 Sept 464
45 Sept 471
I need to delete the first 45 Sept 464
Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Golden_eye (9/16/2013)
Hello,
I did a check in my table and I found these duplicates:
Key Month Usage
45 Sept 464
45 Sept 471
28 Sept 762
28 Sept 766
41 Sept 561
41 Sept 568
47 Sept 862
47 Sept 872
How to delete the duplicate rows with less usage?
For example for
45 Sept 464
45 Sept 471
I need to delete the first 45 Sept 464
Thanks.

Can you post ddl (create table statements) and sample data (insert statements)? I suspect that you might be able to use row_Number here. Once you post some details we can find you a solution pretty quickly and easily.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Mark Cowne One Orange Chip Points: 26968 More actions · Answer 2

May be as simple this

SELECT Key, Month, MAX(Usage) AS Usage

FROM myTable

GROUP BY Key, Month

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Golden_Eye SSC-Addicted Points: 463 More actions · Answer 3

Yes, the last one works within my whole script.

Thanks a lot!

ccavaco Old Hand Points: 302 More actions · Answer 4

To Delete-

WITH CTE AS (SELECT keys, month, MIN(Usage) AS Usage

FROM #deletion

GROUP BY keys, month)

DELETE d

FROM #deletion d

WHERE EXISTS(SELECT * FROM CTE c WHERE d.keys = c.keys AND d.month = d.Month AND d.Usage = c.Usage )