Geek Answers Handbook

Display one rank in MySQL table

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 this row by their value.

i.e. if udid is set = 0000, I have to return 5.

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

+5
source share
2 answers

MySQL does not have any analytic / rank 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

To find out which 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'

MAX , . MAX ORDER BY rank LIMIT 1.

+7

OMG, udid, 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'
+1

More articles:


All Articles