Delete almost duplicates - SQL Server Forums

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

Site Related Forums

Article Discussion

Delete almost duplicates

Author

Topic

ttran
Starting Member

23 Posts

Posted - 2013-05-23 : 19:04:19

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

52326 Posts

Posted - 2013-05-24 : 00:36:53

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

ttran
Starting Member

23 Posts

Posted - 2013-05-24 : 11:56:33

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

ttran
Starting Member

23 Posts

Posted - 2013-05-24 : 12:13:19

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-25 : 09:53:48

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-25 : 10:03:45

i cant find any obvious issues with your query 2

------------------------------------------------------------------------------------------------------
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