CODEWITHSUNDEEP
mysqldatabase
Caspar B
Caspar B

Reputation: 539

MySQL add score column to SELECT high scores query

I have a database named users that contains the following:

userid   name     highscore
0        sam      20
1        james    39
2        jack     3
3        harry    46

Highscore table query

I am using a query found on this post to gather results for my high-score table:

SELECT s.*
FROM users AS s
  JOIN
    ( SELECT DISTINCT highscore
      FROM users
      ORDER BY highscore DESC
          LIMIT 3
    ) AS lim
    ON s.highscore = lim.highscore 
ORDER BY s.highscore DESC ;

This would return the top 3 results, i.e. the rows containing sam, james and harry.

Ranking query

I have a separate query for returning the rank of an individual user in the table:

SELECT 1 + COUNT(*) AS rank 
FROM users 
WHERE highscore > (SELECT highscore FROM users WHERE name = '$name');

Ranks are returned dynamically, rather than stored in a 'rank' column, because they constantly change depending on the highscores of other users.

Goal

I would like to merge the ranking query into the highscore table query, so that each row in the $result contains something along the lines of: ...,"rank":"(users rank)". Is this possible? Thanks.

Upvotes: 1

Views: 842

Answers (1)

nbk
nbk

Reputation: 49375

if you want something like this

userid  name    highscore   rank
3       harry   46          1
1       james   39          2
0       sam     20          3

you can use following query

SELECT s.*, @curRank := @curRank + 1 AS rank
FROM users AS s
JOIN
( SELECT DISTINCT highscore
  FROM users
  ORDER BY highscore DESC
      LIMIT 3
) AS lim
ON s.highscore = lim.highscore 
, (SELECT @curRank := 0) r
ORDER BY s.highscore DESC ;

Upvotes: 2

Related Questions