CODEWITHSUNDEEP
mysqlsqldatabase
SamiulHSohan
SamiulHSohan

Reputation: 167

Leaderboard with rank - MySQL

I have a table like this

+-----+-------+
| pid | score | 
+-----+-------+      
|   1 |    120|          
|   2 |    130|    
|   3 |    100|    
|   1 |    120|        
|   2 |    130|    
|   3 |    100|      
+-----+-------+

I am trying to get get my users ranking. Currently I am using this query, 

SELECT pid
     , SUM(score) AS total_score
  FROM score 
 GROUP 
    BY pid 
 ORDER 
    BY total_score DESC

I am getting a sorted list by score with this query but not getting their position. I want something like this

+-----+-------+------+
| pid | score | rank |
+-----+-------+------+       
|   2 |    260|    1 |       
|   1 |    240|    2 |
|   3 |    200|    3 |       
+-----+-------+------+

And can I get this position for a specific user? Like

+-----+-------+------+
| pid | score | rank |
+-----+-------+------+       
|   1 |    240|    2 |        
+-----+-------+------+

I am using MySQL 5.7.21. Can you please help?

Upvotes: 2

Views: 340

Answers (2)

xkothe
xkothe

Reputation: 674

Yes, you can use LIMIT like:

SELECT pid, SUM(score) AS total_score 
FROM score 
GROUP BY pid 
ORDER BY total_score DESC 
LIMIT 1

Upvotes: 0

A. Colonna
A. Colonna

Reputation: 872

If you have the chance to be in Mysql 8.0.2 or more you have the function rank.

Else You can use a variable to set the rank:

SELECT pid,
       total_score,
       @curRank := @curRank + 1 AS rank
FROM
    (SELECT pid,
            sum(score) AS total_score
     FROM score
     GROUP BY pid
     ORDER BY total_score DESC) datas, (SELECT @curRank := 0) r

You can filter after to get only the result for the pid you want

Upvotes: 3

Related Questions