Displaying a single rank in MySQL table

analytic-functions, mysql, rank, ranking, sql

Solution

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`.

Problem

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?

Original source