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

SQL Server 2000 Forums

Transact-SQL (2000)

Ranking problem

Author

Topic

a691u23
Starting Member

3 Posts

Posted - 2003-03-18 : 15:37:26

I have a table with an ID field that has duplicates

ID
123ABC
123ABC
123ABC
234XYZ
234XYZ

I would like to add another field that would rank or increment each instance of the id field by 1

ID...........RANK
123ABC....1
123ABC....2
123ABC....3
234XYZ....1
234XYZ....2

I have accomplished this fairly quickly using a cursor but I haven't been able to figure out a set based update via a query. Any help or direction would be greatly appreciated.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-03-18 : 15:44:30

try this http://www.sqlteam.com/item.asp?ItemID=3856 ... but use a count(*) aggregate rather than a sum ...

Jay White
{0}

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-18 : 15:48:21

Jay --

the method you gave using count(*) won't work because there is no key field to distinguish between the records .... all of those techniques require some sort of unique key I believe (i may have to double-check that).

I DOUBT this will work properly consistently, but something like:

create table test(ID char(6), count int)
GO

insert into test (ID)
select '123abc' union all
select '123abc' union all
select '123abc' union all
select '456dev' union all
select '456dev' union all
select '456dev'
GO

declare @lastID char(6)
declare @count int

set @lastID = ''
set @count = 0

update test
set @count = case when @lastID = ID then @count + 1 else 1 end,
@lastID = ID,
count = @count
from test

select * from test

GO
drop table test

???????????

- Jeff

Edited by - jsmith8858 on 03/18/2003 15:50:29

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-03-18 : 15:55:02

Jeff, beautiful ...

I was too quick on the draw ... And I've been pushing that method for a long time ... I'm slippin.

Jay White
{0}

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-18 : 16:15:54

Jay -- that method is definitely all you.

I should've given you a plug on that one ...

(I think i meant to but left it out in my usual haste)

jeff

- Jeff

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2003-03-18 : 22:16:52

Hmmm. Maybe one of you should write an article on creative uses of the update statement. :)

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-03-19 : 05:28:01

I wouldn't use it without a safety mechanism -- detecting when the rows are not seen by the UPDATE in the desired order. And from what I remember, it's significantly less likely to go wrong if the table has a clustered index on something that sorts the same as the desired update order.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-03-19 : 06:47:46

When I used this method in this article, I did the the work in a temp table and had an order by on the population of the temp table. That seems to prevent any ordering problems. That being said, I seem to remember someone ranting on this board about there being no such thing as row order in a table (no first, no last, etc..)...

I think I originally adapted this from
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=22342
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=14095

rrb, is the orginal genius on this (no idea where he stole it from

) ... so he may be better equiped to answer this. Also note that rrb adds some checks to his method that I leave out. I don't know if that makes a difference.

Jay White
{0}

a691u23
Starting Member

3 Posts

Posted - 2003-03-19 : 09:30:31

Thanks to Page47 and Jsmith. The code works beautifully. What took 15 minutes to update (1.2 million records) via a cursor took 29 seconds.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-19 : 10:37:25

a691u23 --

as mentioned in this thread, be careful with this method. Definitely run checks to make sure your data got updated properly.

Run this query:

SELECT ID, Rank
FROM Yourtable
GROUP BY ID, Rank
having COUNT(*) <> 1

If any records are returned, something went wrong. If not, then you should be good to go!

- Jeff

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-03-19 : 20:24:35

I agree with Jeff and Arnold, you do have to be careful about the ordering. Be safe and create a clustered index on the table that orders the rows exactly the way you need them to be ordered. I'm using this UPDATE feature on a temp table, and I've had some weird things happen when I didn't put a clustered index on it. Ever since I added one it's been perfectly consistent.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources