Getting the user's position in the leaderboard

Question

Query stopped working (getting the user's position in the leaderboard):

SELECT 
        `rank`, `uid`, `battleWinScore` 
        FROM
            (SELECT 
             @rank:=@rank+1 AS `rank`, `uid`, `battleWinScore` 
             FROM
             `rating`, (SELECT @rank := 0) r 
             ORDER BY `battleWinScore` DESC 
            ) t 
        WHERE uid = 572;
        

In the rating table, we need to get the user's position by field battleWinScore. I am absolutely not good at mysql. Help =)

Answer 1

If you are running MySQL 8.0, just use row_number() (or rank(), if you want to consistently allow ties).

select *
from (
    select uid, battleWinScore, rank() over(order by battleWinScore desc) rn
    from rating
) r
where uid = 572

In earlier versions, I would recommend a correlated subquery rather than user variables:

select uid, battleWinScore
    (select count(*) + 1 from rating r1 where r1.battleWinScore > r.battleWinScore) as rn
from rating r
where uid = 572