Trying to avoid cursors or a loop for this SQL

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

Transact-SQL (2000)

Trying to avoid cursors or a loop for this SQL

Author

Topic

opeterson
Starting Member

1 Post

Posted - 2009-10-02 : 11:32:14

I have a query, building the below temporary table:

SearchResultID (PK) - ContentID - Rank
1 2134 22
2 2134 108
3 2666 66
4 2666 69
5 4567 4
6 4567 123

From this table, I want to remove the duplicate ContentID values, leaving only the one with the highest Rank value - and I will then use the PK of SearchResultID in another delete query.

So after this action, the above table will look like:

SearchResultID (PK) - ContentID - Rank
2 2134 108
4 2666 69
6 4567 123

This was originally done with cursors which was too slow, way too slow. I tried with a While loop, but again was too slow.

Can anyone suggest the fastest way in SQL 2000 to achieve this result?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-02 : 11:38:41

do like this


DELETE t
FROM table t
LEFT JOIN (SELECT ContentID,MAX([Rank]) AS Latest
           FROM table
           GROUP BY ContentID)t1
ON t1.ContentID=t.ContentID
AND t1.Latest=t.[Rank]
WHERE t1.ContentID IS NULL

make sure you first do select and make sure the returning ones are real ones to be deleted

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-03 : 04:11:51

<<
make sure you first do select and make sure the returning ones are real ones to be deleted
>>

I usually advise this for complex deletions

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-03 : 14:37:34

quote:
Originally posted by madhivanan

<<
make sure you first do select and make sure the returning ones are real ones to be deleted
>>

I usually advise this for complex deletions

Madhivanan

Failing to plan is Planning to fail

I wanted OP to make sure his posted sample data reflected real scenario at hand

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources