Author |
Topic |
maddyslayer
Yak Posting Veteran
57 Posts |
Posted - 2013-04-23 : 12:44:33
|
Here's the tables_id cust_id date mdate cus_code cus_state1 200 2/1/2012 4/1/2011 p y2 200 2/2/2012 4/2/2011 p y3 200 2/3/2012 4/3/2011 p y4 200 2/4/2012 4/4/2011 q n5 300 2/5/2012 4/5/2011 r y6 300 2/6/2012 4/6/2011 r y7 300 2/7/2012 4/7/2011 s y8 300 2/8/2012 4/8/2011 s n9 300 2/9/2012 4/9/2011 t y10 400 2/10/2012 4/10/2011 t y11 400 2/11/2012 4/11/2011 u n12 400 2/12/2012 4/12/2011 t yHow 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 likeDELETE tFROM(SELECT ROW_NUMBER() OVER (PARTITION BY cust_id,cus_code,cus_state ORDER BY s) AS Seq,*FROM Table)tWHERE Seq > 1 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 tFROM(SELECT ROW_NUMBER() OVER (PARTITION BY cust_id,cus_code,cus_state ORDER BY mdate DESC, s) AS Seq,*FROM Table)tWHERE 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 tFROM(SELECT ROW_NUMBER() OVER (PARTITION BY cust_id,cus_code,cus_state ORDER BY mdate DESC, s) AS SeqFROM Table)tWHERE 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 tFROM(SELECT ROW_NUMBER() OVER (PARTITION BY cust_id,cus_code,cus_state ORDER BY mdate DESC, s) AS SeqFROM Table)tWHERE 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 MVPhttp://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 tFROM(SELECT ROW_NUMBER() OVER (PARTITION BY cust_id,cus_code,cus_state ORDER BY mdate DESC, s) AS SeqFROM Table)tWHERE 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 MVPhttp://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 MVPhttp://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 tFROM(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 SeqFROM Table)tWHERE Seq > 1[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|