Displaying a single rank in MySQL table

Question

I have a table called 'highscores' that looks like this.

id      udid       name       score
1       1111       Mike       200
2       3333       Joe        300
3       4444       Billy      50
4       0000       Loser      10
5       DDDD       Face       400

Given a specific udid, I want to return the rank of that row by their score value.

i.e. if udid given = 0000, I should return 5.

Any idea how to write this query for a MySQL database?

Conservatory

Answer 1

MySQL doesn't have any analytic/ranking functionality, but you can use a variable to artificially create a rank value:

  SELECT t.id,
         t.udid,
         t.name,
         t.score,
         @rownum := @rownum + 1 AS rank
    FROM HIGHSCORES t
    JOIN (SELECT @rownum := 0) r
ORDER BY t.score DESC

In order to see what rank is associated with UDID "0000", use:

SELECT MAX(x.rank) AS rank
  FROM (SELECT t.id,
               t.udid,
               t.name,
               t.score,
               @rownum := @rownum + 1 AS rank
          FROM HIGHSCORES t
          JOIN (SELECT @rownum := 0) r
      ORDER BY t.score DESC) x
 WHERE x.udid = '0000'

Need the MAX for if the user has multiple high score values. Alternately, you could not use MAX and use ORDER BY rank LIMIT 1.

Slifka

ahh thanks, there is only 1 entry per udid, I take care of that in my php script. also, I'm a little knew to this. So how do the two blocks work together. Do I put them in the same query? Or right after each other? – Conservatory

@MichaelInno: If you are only checking for a specific users rank, you only need to use the second query. Replace the '0000' with whatever udid you are looking for. – Slifka

This will give you different ranks for users with the same score. – Lepidosiren

@Josef: Yes - most don't want ties to have the same rank value. – Slifka

Answer 2

To reiterate OMG's excellent answer which is the general case of multiple high scores per udid, here's the query based on the precondition of exactly one entry per udid:

SELECT rank
FROM
   (SELECT @rownum := @rownum + 1 AS rank, score, udid
    FROM highscores
    JOIN (SELECT @rownum := 0) r
    ORDER BY highscores.score DESC) x
WHERE x.udid = '0000'

Mealtime