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?

score 7 · Answer 1 · answered Jan 28 '10 at 21:17

7

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.

answered Jan 28 '10 at 21:17

OMG Ponies

325,700
82
523
502

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? – Jan 28 '10 at 21:28
@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. – OMG Ponies Jan 28 '10 at 21:39
This will give you different ranks for users with the same score. – Josef Pfleger Nov 06 '11 at 19:53
@Josef: Yes - most don't want ties to have the same rank value. – OMG Ponies Nov 06 '11 at 21:08

score 1 · Answer 2 · answered Jan 28 '10 at 22:15

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'

Displaying a single rank in MySQL table

2 Answers2