Re: Re: What's an Efficient Way to Retrieve a Rank from a MySQL Table?

I went through your suggestion and here are my comments:

Assuming we have the following data:

student - score
a - 70
b - 70
c - 70
d - 60
e - 50

problem: we need to determine the rank for student d

From your above suggestion, the result of your query would be "4" (meaning you are the fourth in the list). However since students a, b, and c have same grades, they should all be ranked as "first" while student "d" should be ranked as "second".

However, your suggestion is very fast and extremely useful if we present the result this way ... "There are 4 students that have higher scores!".

Would the "SELECT" command return the number of rows returned? If so, we can slightly modify your above suggestion to return results GROUPed by the scores. If the scores are grouped, the modified query would return only 1 row since only the score of 70 is greater than 60. If rank is one more than the number of results that are better, then the rank is correctly reported as "2".

Comment on Re: Re: What's an Efficient Way to Retrieve a Rank from a MySQL Table?

Replies are listed 'Best First'.
Re: What's an Efficient Way to Retrieve a Rank from a MySQL Table? by Abigail-II (Bishop) on May 04, 2004 at 11:56 UTC
Ranking student 'd' as second is very unrealistic. It would mean that if student 'a' (who is better than 'd') increases his score to 70.000001, student 'd' drops a rank. That would be counterintuitive. Or, if you are a sports fan, if these scores would be sports scores, 'd' would not get a silver medal. Having said that, look into the 'group by' clause. What you want is determine the rank of the group the student falls in. Abigail	[reply]
Re: Re: What's an Efficient Way to Retrieve a Rank from a MySQL Table? by soon_j (Scribe) on May 04, 2004 at 12:08 UTC
You have a point. But in my application, it is necessary. I have to rank students in terms of the number of questions a student answered, ... in terms of the total duration of his exam, ... in terms of his average speed (in sec/question) Which means that if student "a" is 0.01 sec faster than the rest, then by all means I have to rank him higher that the rest. BTW, only two decimal places are being reported under some columns. Had it been several decimal places like the you pointed ... 70.000001.. I would definitely consider your suggestion. Thanks. By far, your suggestions are the most optimal.	[reply]
Re: What's an Efficient Way to Retrieve a Rank from a MySQL Table? by Abigail-II (Bishop) on May 04, 2004 at 12:13 UTC
My point is that whether student "a" is 0.01 sec faster than student "b" or "c" should not effect student "d"'s ranking. Why would your ranking depend on the number of ties between the people who are better than you? "Daddy, daddy, I was second of my class! All other 29 students beat me!" Oh well, it's your application, not mine. ;-) Abigail	[reply]
Re: Re: What's an Efficient Way to Retrieve a Rank from a MySQL Table? by soon_j (Scribe) on May 04, 2004 at 12:25 UTC
Re^3: What's an Efficient Way to Retrieve a Rank from a MySQL Table? by pelagic (Priest) on May 04, 2004 at 12:01 UTC
It actually depends whether you want to rank a) the students or b) their scores: a) student d ist 4th b) student d has 2nd best score pelagic	[reply]
Re: Re^3: What's an Efficient Way to Retrieve a Rank from a MySQL Table? by soon_j (Scribe) on May 04, 2004 at 12:14 UTC
You have a point. It's safe to say that student "d" has the 2nd best score and the 4th from the list. However, let's say that student "d" and another student "f" has the same score, we still safely say that student "d" has the 2nd best score (together with "f"), but is student "d" right now still the 4th or would he be the 5th? We can't directly tell, except if we add another criteria that makes student "d" be listed as 5th and student "f" as 4th. Thanks.	[reply]
Re: What's an Efficient Way to Retrieve a Rank from a MySQL Table? by pelagic (Priest) on May 04, 2004 at 13:00 UTC
These scores: `student - score a - 70 b - 70 c - 70 d - 60 f - 60 e - 50` [download] simply must lead to following ranking: `Students Rank: 1. a,b,c 4. d,f 6. e` [download] Ranking the score-values is of little practical use. As Abigail mentionned above: 50 is the 3rd Score (if you rank scores without considering their actual occurrence). But student e with score 50 was last of 6. pelagic	[reply] [d/l] [select]