Ozan Kurt Ozan Kurt - 4 months ago 25
MySQL Question

SQL User Score ranking by grouping

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

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