Creating a rank column with sorted list

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

SQL Server Development (2000)

Creating a rank column with sorted list

Author

Topic

dingodog
Starting Member

5 Posts

Posted - 2006-12-18 : 11:38:16

I have a table with data and I'm trying to pull out the top x results with a ranking column for each result.
For example I can pull out the following results:

Name Games Goals Assists Points
Crosby 29 16 36 52
Jagr 34 14 33 47
Ovechkin 32 22 21 43
St.Louis 33 20 23 43
Spezza 34 19 24 43
Selanne 35 19 24 43
Hossa 34 22 20 42
etc...

I use the following to get the top 25 in scoring:
SELECT * FROM players_rs as a
where a.points in (select top 25 b.points from players_rs as b order by b.points desc, goals desc, games asc)
order by a.points desc, goals desc, games asc;

However, I want to include the rank as the first column (from 1 to 25). That the first problem. I can't seem to resolve the select statement with a counter.
To get even trickier, I would like ties to be accounted for and ranked accordingly. For example:
Rank Name Games Goals Ass Points
16 Sakic 33 15 23 38
17 Kozlov 34 11 26 37
17 Savard 34 11 26 37
19 Kovalchuk 34 18 18 36
19 Afinogenov 34 18 18 36
21 Brind'Amour 30 10 25 35

I'm only a casual SQL programmer, so there is probably an easier way to do the whole thing at once.

Thanks,
dingodog

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-18 : 11:44:49

sql2005 style
select top 25 *, dense_rank() over (order by points desc, goals desc, games)
from players_rs
order by points desc, goals desc, games

sql2000 style
select top 25 b.*, (select count(distinct points) from players_rs a where a.points <= b.points)
from players_rs b
order by points desc, goals desc, games

Peter Larsson
Helsingborg, Sweden

dingodog
Starting Member

5 Posts

Posted - 2006-12-18 : 13:12:21

That getting closer but still two problems running that one (in sql2000):
1. the ranking is backwards. When I run that it gives me the ranking:
5
4
3
2
1
while I want it to be:
1.2.3.4.5.

2. When running your method I would get the following results:

Rank Player Goals Assists Points
1 Sundin 4 0 4
1 Forsberg 3 1 4
1 Naslund 2 2 4
1 Lidstrom 1 3 4
1 Sedin 0 4 4

How do I create the rank to be equivalent to the way I create the sort order (by points desc, goals desc, games asc) such as the following:

Rank Player Goals Assists Points
1 Salming 4 0 4
2 Steen 3 1 4
3 Loob 2 2 4
4 Jonsson 1 3 4
5 Gradin 0 4 4

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-18 : 13:21:45

Create a calculated column with this formula

1000000 * (999 - Points) + 1000 * (999 - Goals) + Games

and then use this query

select top 25 b.*, (select count(distinct peso) from players_rs a where a.peso >= b.peso)
from players_rs b
order by b.peso

Peter Larsson
Helsingborg, Sweden

dingodog
Starting Member

5 Posts

Posted - 2006-12-19 : 10:09:43

I like that work-around, however Points is a calculated column (from goals + assists) and it doesn't appear to allow you to use a calculated column in a formula for another calculated column.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-19 : 11:04:39

1000000 * (999 - Goals - Assists) + 1000 * (999 - Goals) + Games

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-19 : 23:53:35

1 Where do you want to show data?
2 If you use front end application, do numbering there

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources