CODEWITHSUNDEEP
mysql
Caspar B
Caspar B

Reputation: 539

MySQL - Assign same rank to users with the same score

I asked this question earlier about generating a rank table that contained the top 3 scores, plus the logged-in user's score, from a list of players. The query looks like this:

SELECT *
  FROM 
     ( SELECT a.*
            , @rank := @rank+1 rank
         FROM users a
         JOIN (SELECT @rank:=0) vars
        ORDER 
           BY highscore DESC
            , userid 
     ) x
 WHERE name = 'jennie'
    OR rank <= 3
     ORDER 
        BY rank;

This fiddle provided by the accepted answer of my previous question demonstrates the query in use.

But, the contents of the rank column provided by this query are technically row numbers, not true ranks - which means when two users have the same score, they will receive different ranks. How could I give unique users with the same score equal ranks - for example, a valid result would be:

userid  name    highscore   rank
3       harry   50          1
1       james   50          1
0       sam     20          2
4       jenny   7           4

Upvotes: 3

Views: 1112

Answers (2)

R.K.Saini
R.K.Saini

Reputation: 2708

Try this: 

SELECT *,
(SELECT COUNT(DISTINCT highscore) FROM my_table r
WHERE s.highscore<=r.highscore) as rank
FROM my_table s
order by rank ASC

https://www.db-fiddle.com/f/nypySJqctEdemBAy3a3mp3/1

Upvotes: 1

Rakib
Rakib

Reputation: 145

Closest solution to your answer I can think of is something like below

SELECT a.userid, a.name, a.highscore,
FIND_IN_SET( a.highscore, 
    (SELECT GROUP_CONCAT( highscore ORDER BY highscore DESC) FROM my_table )) AS rank
FROM my_table a;

Here GROUP_CONCAT function is used to aggregate all high scores in an ordered string and after that with FIND_IN_SET function we can locate the position of our desired score from the selected aggregated result.

Upvotes: 2

Related Questions