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
 Site Related Forums
 Article Discussion
 Delete almost duplicates
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ttran
Starting Member

USA
23 Posts

Posted - 05/23/2013 :  19:04:19  Show Profile  Reply with Quote
I've struggled... trying to delete almost duplicate (not exactly duplicate) rows in a table.

table_A:
ID CD SQ ESD EED
1 r 1 aaa bbb
2 r 1 ccc ddd
2 r 2 ccc ddd
3 f 4 aaa bbb
3 f 5 aaa bbb
3 f 6 aaa bbb
4 r 2 ccc ddd
5 f 6 aaa bbb
5 f 7 aaa bbb

As you can see... the ID 2 are not exactly duplicated because the SQ column are different. But the SQ column is not important to database.
I want to keep only highest SQ if the rest of the columns are the same.
As being said, the rows should be kept:
ID CD SQ ESD EED
1 r 1 aaa bbb
2 r 2 ccc ddd
3 f 6 aaa bbb
4 r 2 ccc ddd
5 f 7 aaa bbb

And this is what I already did:
(1) I created a temporary table_B (will be deleted after the process is done) contains all the duplicate rows:

select id,cd,esd,eed,count(*)as DupCount,max(sq) as maxsq into table_B from table_A group by id,cd,esd,eed having count(*) > 1

(2) Then, I tried to delete the rows from table_A which have the sq != maxsq. But before deleting these rows from table_A, I list them for verification:

select a.*
from table_A a join table_B b on a.id = b.id and a.cd = b.cd and a.esd = b.esd and a.eed = b.eed
where a.sq != b.maxsq

To my surprise... I got nothing listed.
I don't know what I did wrong.

Any help or suggestion would be greatly appreciated.
Thank you.

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/24/2013 :  00:36:53  Show Profile  Reply with Quote
you just need this


DELETE t
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY ID, CD, ESD, EED ORDER BY SQ DESC) AS Rn
FROM YourTable
)t
WHERE Rn >1



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

ttran
Starting Member

USA
23 Posts

Posted - 05/24/2013 :  11:56:33  Show Profile  Reply with Quote
It is simple and work well.
Thank you...thank you so much, Visakhm16.
Go to Top of Page

ttran
Starting Member

USA
23 Posts

Posted - 05/24/2013 :  12:13:19  Show Profile  Reply with Quote
visakhm16,

Your query is perfect.
But could you tell me why my 2nd query doesn't work?
I just want to learn and avoid mistakes in the future.

Thanks again.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/25/2013 :  09:53:48  Show Profile  Reply with Quote
quote:
Originally posted by ttran

It is simple and work well.
Thank you...thank you so much, Visakhm16.



you're welcome

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

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/25/2013 :  10:03:45  Show Profile  Reply with Quote
i cant find any obvious issues with your query 2

------------------------------------------------------------------------------------------------------
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.08 seconds. Powered By: Snitz Forums 2000