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

SQL Server Development (2000)

Ranking problem

Author

Topic

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2002-03-01 : 00:44:48

Hi there,

I have the following test table/result set:

ID POINTS
----------- -----------
1 25
2 15
3 10
4 150
5 12
6 149
7 15
8 12

Which I would like to return a ranking from. that ranking ordered by the highest points - so no 1 ranking would be 150, no 2 would be 149 and so on.

I have the following query:

SELECT Rank = count(*),
S1.points, S1.id
FROM test S1, test S2
WHERE S1.Points <= S2.Points
GROUP BY S1.ID, S1.Points
ORDER BY s1.POINTS DESC

which returns:

Rank points id
----------- ----------- -----------
1 150 4
2 149 6
3 25 1
5 15 2
5 15 7
7 12 5
7 12 8
8 10 3

which is close but it skips the 4 and 6 ranking positions? Am I going about this the wrong way? (probably obvious but Im fried today!)

thanks - matt.

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2002-03-01 : 00:45:59

oops - silly me thought that those tabs would line up nicely! please forgive the formatting of the result set.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-03-01 : 00:50:28

How are you meant to determine which ID is higher if they both have the same score?

Damian

Edited by - merkin on 03/01/2002 00:50:48

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2002-03-01 : 01:09:11

Hi Merkin,

I dont want tied results to have individual rankings - just set out so rankings are not skipped!

cant it be done so that tied results are just flagged as the next ranking in order? so instead of the ties on the point 15 being ranked 5 - they are actually ranked 4th and then the ties on 12 will be ranked 5 and so on.

This way I can have a leader board and no-one asking "wheres 4th?" or "where's 6th?".

Does this make sense?

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-03-01 : 04:37:11

Change the COUNT(*) to COUNT(DISTINCT S2.Points)

nricardo
Starting Member

17 Posts

Posted - 2002-03-01 : 10:58:29

quote:

This way I can have a leader board and no-one asking "wheres 4th?" or "where's 6th?".

Not SQL related, but won't people say "Hey! I'm in fourth place, but I have the same number of points as third place!"

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2002-03-01 : 19:53:15

Thanks Arnold Fribble - works great!

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2002-03-01 : 23:23:38

0k,

now ive got another column that I need the result set ordered by. Take the following results from a view:

MEMBERID MARGIN TOTALWINNINGTIPS
----------- ----------- ----------------
6 277 7
9 29 7
10 36 8
11 21 8
12 40 7

I need to do the ranking on this view but ordered by the totalwinningtips number DESC and then the MARGIN ASC. So the highest TOTALWINNINGTIPS goes first followed by the lowest Margin etc.

I have the following query:

SELECT Rank = COUNT(DISTINCT S2.MARGIN),
S1.MARGIN, S1.TOTALWINNINGTIPS
FROM vw_MARGINS_AND_TIPS S1, vw_MARGINS_AND_TIPS S2
WHERE S1.MARGIN >= S2.MARGIN AND S1.TOTALWINNINGTIPS <= S2.TOTALWINNINGTIPS
GROUP BY S1.MARGIN, S1.TOTALWINNINGTIPS
ORDER BY s1.TOTALWINNINGTIPS DESC, s1.MARGIN ASC

which gives me the result set:

Rank MARGIN TOTALWINNINGTIPS
----------- ----------- ----------------
1 21 8
2 36 8
2 29 7
4 40 7
5 277 7

but I am back to square one in that there is no "3rd Ranking" etc. I have tried COUNT(DISTINCT S2.MARGIN, TOTALWINNINGTIPS ) but am getting query errors.

Thanks again for any help on this.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-03-04 : 06:36:26

Yes, this sort of thing gets messy in SQL.
I think you'll need to do something like this:


SELECT Rank = COUNT(*), MARGIN, TOTALWINNINGTIPS
FROM (
  SELECT S1.MARGIN, S1.TOTALWINNINGTIPS
  FROM vw_MARGINS_AND_TIPS S1, vw_MARGINS_AND_TIPS S2
  WHERE S1.TOTALWINNINGTIPS < S2.TOTALWINNINGTIPS
    OR (S1.TOTALWINNINGTIPS = S2.TOTALWINNINGTIPS AND S1.MARGIN => S2.MARGIN)
  GROUP BY S1.MARGIN, S1.TOTALWINNINGTIPS, S2.MARGIN, S2.TOTALWINNINGTIPS) a
GROUP BY MARGIN, TOTALWINNINGTIPS
ORDER BY TOTALWINNINGTIPS DESC, MARGIN ASC

Here, the counting of distinct (MARGIN, TOTALWINNINGTIPS) is done by grouping twice. Note that, unlike COUNT(DISTINCT), this technique will count NULLs.
The other change is the WHERE condition: if you want to count rows that sort not-after, then you'll need that OR. Unfortunately, ORs in WHERE conditions are hard for the optimizer to deal with, so this may well result in a significantly slower query.

An alternative is to create a single value that sorts as required for the ranking. This tends to result in a rather fragile query, but it may be faster (or slower!):


SELECT Rank = COUNT(DISTINCT (S2.TOTALWINNINGTIPS*65536 - S2.MARGIN)),
  S1.MARGIN, S1.TOTALWINNINGTIPS
FROM vw_MARGINS_AND_TIPS S1, vw_MARGINS_AND_TIPS S2
WHERE (S1.TOTALWINNINGTIPS*65536 - S1.MARGIN)
  <= (S2.TOTALWINNINGTIPS*65536 - S2.MARGIN)
GROUP BY S1.MARGIN, S1.TOTALWINNINGTIPS
ORDER BY s1.TOTALWINNINGTIPS DESC, s1.MARGIN ASC

(Haven't tested these queries)

Edited by - Arnold Fribble on 03/04/2002 15:01:55

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2002-03-04 : 18:15:09

Hi Arnold,

Thanks for your response.

I have used a query similar to your first example which I found at microsoft.sqlserver newsgroups:

select (select count(DISTINCT b.margin)
from vw_MARGINS_AND_TIPS as b
where b.TOTALWINNINGTIPS > a.TOTALWINNINGTIPS
or ( b.TOTALWINNINGTIPS = a.TOTALWINNINGTIPS and
b.margin <= a.margin) ) as rank,
a.margin ,
a.TOTALWINNINGTIPS, a.memberid
from vw_MARGINS_AND_TIPS as a
WHERE a.COMPANYID = @COMPANYID
order by rank

This seems to work fine but I still have further testing just to be sure!

Thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources