Deleting duplicate rows except for one

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Deleting duplicate rows except for one

Author

Topic

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 2013-04-23 : 12:44:33

Here's the table

s_id cust_id date mdate cus_code cus_state
1 200 2/1/2012 4/1/2011 p y
2 200 2/2/2012 4/2/2011 p y
3 200 2/3/2012 4/3/2011 p y
4 200 2/4/2012 4/4/2011 q n
5 300 2/5/2012 4/5/2011 r y
6 300 2/6/2012 4/6/2011 r y
7 300 2/7/2012 4/7/2011 s y
8 300 2/8/2012 4/8/2011 s n
9 300 2/9/2012 4/9/2011 t y
10 400 2/10/2012 4/10/2011 t y
11 400 2/11/2012 4/11/2011 u n
12 400 2/12/2012 4/12/2011 t y

How do I delete duplicates that have same cust_id,cus_code,cus_state but keeping one?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-23 : 12:57:48

if you're not concerned about cus_state value of retained row then use logic like


DELETE t
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY cust_id,cus_code,cus_state ORDER BY s) AS Seq,*
FROM Table
)t
WHERE Seq > 1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 2013-04-24 : 10:19:29

If there are two records with same cust_id,cus_code but different cus_state, how do i delete the record with older mdate?

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-04-24 : 11:33:06

by changing the order by clause like:
[CODE]

DELETE t
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY cust_id,cus_code,cus_state ORDER BY mdate DESC, s) AS Seq,*
FROM Table
)t
WHERE Seq > 1

[/CODE]

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-24 : 12:00:31

Not an answer to your question, but just an interesting observation - In MuMu's query and in Visakh's query, you don't need to return any other columns from the inner select.

DELETE t
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY cust_id,cus_code,cus_state ORDER BY mdate DESC, s) AS Seq
FROM Table
)t
WHERE Seq > 1

Don't yawn! It seemed interesting to ME!! Oh well!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-24 : 12:44:27

quote:
Originally posted by James K

Not an answer to your question, but just an interesting observation - In MuMu's query and in Visakh's query, you don't need to return any other columns from the inner select.
DELETE t
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY cust_id,cus_code,cus_state ORDER BY mdate DESC, s) AS Seq
FROM Table
)t
WHERE Seq > 1
Don't yawn! It seemed interesting to ME!! Oh well!!!

i always do * as i first run the select alone to make sure i get the records in correct sequence before i apply the delete to remove the duplicates

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-24 : 13:21:18

quote:
Originally posted by visakh16

quote:
Originally posted by James K

Not an answer to your question, but just an interesting observation - In MuMu's query and in Visakh's query, you don't need to return any other columns from the inner select.
DELETE t
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY cust_id,cus_code,cus_state ORDER BY mdate DESC, s) AS Seq
FROM Table
)t
WHERE Seq > 1
Don't yawn! It seemed interesting to ME!! Oh well!!!

i always do * as i first run the select alone to make sure i get the records in correct sequence before i apply the delete to remove the duplicates

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

I do that too, but when I run the inner query by itself to make sure I have the correct ordering, I use only a sample of the data, such as TOP 100.

For the actual delete, especially if the rows affected is large, I exclude the * in the hope that I won't force SQL Server to bring over all the data. May be the query optimizer is smart enough to figure out that it doesn't need to bring over all the columns including row overflow data and such even if I didn't do that.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-25 : 00:56:31

In actual scenarios I remove it before i turn it into DELETE. here I was lazy enough not to remove the * bit before i posted

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 2013-04-25 : 13:00:07

If the cust_id has a date and mdate, how do i keep the record with recent date(doesn't matter if it's date or mdate) and delete the record with older date(doesn't matter if it's date or mdate)?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-25 : 13:02:26

[code]
DELETE t
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY cust_id,cus_code,cus_state ORDER BY CASE WHEN date > mdate THEN date ELSE mdate END DESC) AS Seq
FROM Table
)t
WHERE Seq > 1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 2013-04-25 : 14:48:27

What happens when mdate is NULL?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-26 : 00:20:19

it will come as last entry in group and will get deleted.
is that your intended result?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources