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..?

What is the use case of this? Do you want to output the result with ranking or just show it like that? — dscham, Sep 22 '18 at 20:17
@i0N77 . . . Is `ostats` actually a column in the `stats` table? — Gordon Linoff, Sep 22 '18 at 20:37
Yes, ostats is a name of a column in stats table @Gordon Linoff — i0N77, Sep 22 '18 at 20:39
@iON77 i meant if you can attach the rank somwhere in the code rather than the SQL statement — dscham, Sep 22 '18 at 20:42
That would be nice @Sens. Php code will do fine too, but I don't know where to begin, probably something to do with Increment operators, but how do I rank users using php code in order of their 'ostats' DESC — i0N77, Sep 22 '18 at 20:44

score 1 · Accepted Answer · answered Sep 22 '18 at 20:27

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;

answered Sep 22 '18 at 20:27

Salvatore Q Zeroastro

758
1
8
17

I get the same error as listed above while using your query too. – i0N77 Sep 22 '18 at 20:42

Gordon Linoff · Answer 2 · 2018-09-22T20:40:29.320

You can assign a row number using variables:

SELECT u.user_id,u.user_uid, s.ostats, s.attack, s.defense,
       s.ranking
FROM (SELECT s.*, (@rn := @rn + 1) as ranking
      FROM (SELECT s.* FROM stats s ORDER BY s.ostats DESC) s CROSS JOIN
           (SELECT @rn := 0) params
     ) s JOIN
     users u 
     ON s.id = u.user_id
ORDER BY s.ostats DESC;

In the event of ties, this will give different users different rankings. If that is an issue, you can use this modified form:

SELECT u.user_id,u.user_uid, s.ostats, s.attack, s.defense,
       s.ranking
FROM (SELECT s.*,
             (@rn := if(@o = ostats, @rn,
                        if(@o := ostats, @rn + 1, @rn + 1)
                       )
             ) as ranking
      FROM (SELECT s.* FROM stats s ORDER BY s.ostats DESC) s CROSS JOIN
           (SELECT @rn := 0, @o := -1) params
     ) s JOIN
     users u 
     ON s.id = u.user_id
ORDER BY s.ostats DESC;

Of course, in MySQL 8.0, you can use row_number(), rank() or dense_rank() for this purpose.

I get an error when I used both of your queries, Please guide me as to where I went wrong with it? check the original post, where I've updated all the code I'm using to query and display it out — i0N77, Sep 22 '18 at 20:33
@i0N77 . . . What error are you getting? What version of MySQL are you using? — Gordon Linoff, Sep 22 '18 at 20:34

Assign and display ranks to users from mysql data

2 Answers2