SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Deleting duplicate rows except for one
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 04/23/2013 :  12:44:33  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 04/23/2013 :  12:57:48  Show Profile  Reply with Quote
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
Go to Top of Page

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 04/24/2013 :  10:19:29  Show Profile  Reply with Quote
If there are two records with same cust_id,cus_code but different cus_state, how do i delete the record with older mdate?
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 04/24/2013 :  11:33:06  Show Profile  Reply with Quote
by changing the order by clause like:


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



Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3707 Posts

Posted - 04/24/2013 :  12:00:31  Show Profile  Reply with Quote
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!!!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/24/2013 :  12:44:27  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3707 Posts

Posted - 04/24/2013 :  13:21:18  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/25/2013 :  00:56:31  Show Profile  Reply with Quote
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

Edited by - visakh16 on 04/25/2013 00:56:49
Go to Top of Page

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 04/25/2013 :  13:00:07  Show Profile  Reply with Quote
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)?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/25/2013 :  13:02:26  Show Profile  Reply with Quote

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 04/25/2013 :  14:48:27  Show Profile  Reply with Quote
What happens when mdate is NULL?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/26/2013 :  00:20:19  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000