Ranking rows
-
Is there a function in SQL Server 2000 that performs the same tasks as Oracle's Rank() function? From the Oracle documentation on Rank():
"RANK calculates the rank of a value in a group of values. Rows with equal values for the ranking criteria receive the same rank. Oracle then adds the number of tied rows to the tied rank to calculate the next rank. Therefore, the ranks may not be consecutive numbers."
Given a table of values (we'll call it 'player_scores') such as:
(playerID identifies a player of a fictional game where scores are ranked. Score is the amount of points the player has)
playerID | score
55, 100
56, 105
57, 107
58, 105
We could query this table like so:
select playerID, score, rank() over (order by score) as rank from player_scores;
playerID | score | rank
57, 107, 1
56, 105, 2
58, 105, 2
55, 100, 4
Please let me know if this is not clear and I will do my best to clarify the scenario. Go easy on me, I'm a newbie poster here. 🙂
-
If I understand correctly, there is nothing like this. You can do a TOP and an ORDER BY, which would tell you which row was in what order, but there would not be an explicit value in some column.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
I realize this is two years later but here's a way to simulate the rank function.
create table #x ( playerID int, score int );
insert into #x ( playerID, score )
select 55,100
union all select 56,105
union all select 57,107
union all select 58,105;
select x.playerID, x.score,
rank = ( select count(*) from #x x2 where x2.score > x.score ) + 1
from #x x
order by rank;
drop table #x;
-
Thanks, John, your post came at just the right time for me.
-
Hi,
I am balavenkatesh, i also want this type function, it works fine, but i want, rank should be as 3 instead of 4 after the rank 2 I attach the result sets Pl help.
Result sets
Player Score Rank
57 107 1
58 105 2
56 105 2
55 100 4
Regards,
Balavenkatesh,
-
Try replacing "count(*)" with "count(distinct score)".
Is that what you mean?
J
-
John,
-
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply