in reply to What's an Efficient Way to Retrieve a Rank from a MySQL Table?

Pseudo SQL code: 
SELECT  1 + COUNT(*) 
  FROM  student_data
 WHERE  grade > (SELECT  grade 
                   FROM  student_data
                  WHERE  name = "whatever")

[download]
The principle being that someones rank is one more than the number of people that are better. I don't think MySQL supports nested SELECT statements, but you can always split the query into two. If you have an index on the 'grade' column (for simplicity, I assume such a column exists), the query should be pretty fast as the database doesn't need to do a table scan.

Abigail

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

Replies are listed 'Best First'.
Re: Re: What's an Efficient Way to Retrieve a Rank from a MySQL Table?
by soon_j (Scribe) on May 04, 2004 at 11:05 UTC
    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".
[reply]
      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]

        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]
      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]

        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: Re: What's an Efficient Way to Retrieve a Rank from a MySQL Table?
by soon_j (Scribe) on May 04, 2004 at 10:18 UTC
    Thank you very much... I see no reason why this shouldn't work... except that MySQL 4.0.18 may not support nested SELECT's (I'm not sure though)... but 4.1.xx may support this. Anyway I can break the queries as you suggested.
[reply]

In Section Seekers of Perl Wisdom