SQL User Score ranking by grouping

Question

I have the following ranking system.

SET @1=0;

SELECT id, username, magic_xp, @i:=@i+1 AS rank
  FROM hs_users
 ORDER 
    BY magic_xp DESC;

hs_users
id  username     magic_xp rank
988 5hapescape   14926854    1
737 Ozan         13034431    2
989 Kurt         13034431    3
  6 LEGACY              0    4
 11 Bobby               0    5
276 Bobby123            0    6
345 Mynamesjason        0    7
450 Demon Spawn         0    8
987 Satan               0    9

As you see I have 2 users have the same xp.

I want to make them both have rank = 2 and the rest should follow from 3.

How can I group them like this?

|  username  | magic_xp | rank |
| ---------- + -------- + ---- |
| ShapeScape |     1000 |    1 |
| Kurt       |      100 |    2 |
| Ozan       |      100 |    2 |
| Legacy     |       10 |    3 |

Answer 1

query

set @i := 0;
set @lagxp := null;

select id, username, magic_xp, 
@i := if(@lagxp = magic_xp, @i,
          if(@lagxp := magic_xp, @i + 1, @i + 1)) as rank
from hs_users
order by magic_xp desc
;

or

SELECT id, username, magic_xp, 
IF (@score=hs_users.magic_xp, @rank:=@rank, @rank:=@rank+1) as rank,
@score:=hs_users.magic_xp score
FROM hs_users, (SELECT @score:=0, @rank:=0) r
ORDER BY magic_xp DESC;

output

+-----+------------+----------+------+----------+
| id  |  username  | magic_xp | rank |  lagxp   |
+-----+------------+----------+------+----------+
| 988 | Shapescape | 14926894 |    1 | 14926894 |
| 737 | Ozan       | 13034431 |    2 | 13034431 |
| 989 | Kurt       | 13034431 |    2 | 13034431 |
|   6 | Legacy     |        0 |    3 |        0 |
+-----+------------+----------+------+----------+

sqlfiddle

Answer 2

In MySQL, the most efficient way is to use variables:

  select t.*,
         (@rank := if(@magic_xp = magic_xp, @rank,
                      if(@magic_xp := magic_xp, @rank + 1, @rank + 1)
                     )
         ) as rank
  from table t cross join
       (select @rank := 0, @magic_xp := NULL) params
  order by magic_xp desc;

Note the complicated expression for the variables. The assignment of both variables is in a single expression. This is on purpose. MySQL does not guarantee the order of assignment of expressions in a SELECT, and sometimes, it does not even evaluate them in order. A single expression is the safe way to do this logic.

A more standard approach in SQL is to use a correlated subquery:

select t.*,
       (select count(distinct t2.magic_xp)
        from table t2
        where t2.magic_xp >= t.magic_xp
       ) as rank
from table t;

Answer 3

Sound the solution :)

SELECT id, username, magic_xp, 
IF (@score=hs_users.magic_xp, @rank:=@rank, @rank:=@rank+1) as rank,
@score:=hs_users.magic_xp score
FROM hs_users, (SELECT @score:=0, @rank:=0) r
ORDER BY magic_xp DESC;

Thanks to @amdixon

Answer 4

select 
  @rank:=if(magic_xp=@prev_magic_xp,@rank,@rank+1) as rank,
  username,
  magic_xp,
  @prev_magic_xp:=magic_xp as prev_magic_xp

from user,(select @rank:=0,@prev_magic_xp="") t

order by magic_xp desc

For your reference: http://sqlfiddle.com/#!9/09bb3/2