Assign and display ranks to users from mysql data

Question

SELECT u.user_id, u.user_uid, s.ostats, s.attack, s.defense
FROM stats s JOIN
     users u 
     ON s.id = u.user_id
ORDER BY s.ostats DESC;

So in above data, "ostats"(overall) is just a sum of attack+defense and by using this query I could display users in descending order of their "ostats" values..

But how do I assign and display rank of each user, like the one with most "ostats" valued user as Rank 1 and the second highest "ostats" valued user as Rank 2 and so on..?

Answer 1

What about using a variable to keep track of the row number?

SET @rank = 0;

SELECT 
    u.user_id,
    u.user_uid, 
    s.ostats, 
    s.attack, 
    s.defense,
    (@rank:=@rank + 1) AS rank
FROM stats s 
    JOIN users u on s.id = u.user_id 
ORDER BY s.ostats DESC;