Ad

Mysql Row_number Rank Based On Value

- 1 answer

I'm trying to find out someones ranking based on value in db. But I can't figure out row_number() here.

Base query, to which I want to add ranks:

select player_id,value from player_storage where `key` = 40001 order by value desc;
+-----------+-------+
| player_id | value |
+-----------+-------+
|        10 |   333 |
|        11 |    31 |
|        15 |    12 |
|         9 |     3 |
|         1 |     0 |
|         8 |     0 |
|        12 |     0 |
|        13 |     0 |
|        14 |     0 |
+-----------+-------+

Lets say I'm player_id = 11, and I want to know what place in ranking I have.

Tried several solutions, like row_number() over (partition by value), but calculated rank is always wrong. Examples:

select player_id,value,row_number() over (partition by value order by value desc) as rank from player_storage where `key` = 40001 order by rank;
+-----------+-------+------+
| player_id | value | rank |
+-----------+-------+------+
|         1 |     0 |    1 |
|         9 |     3 |    1 |
|        10 |   333 |    1 |
|        11 |    31 |    1 |
|        15 |    12 |    1 |
|         8 |     0 |    2 |
|        12 |     0 |    3 |
|        13 |     0 |    4 |
|        14 |     0 |    5 |
+-----------+-------+------+

Or...

set @rank = 0 ; select rank,player_id,value from (select player_id,value,(@rank:[email protected]+1) as rank from player_storage, (select @rank := 0) r where `key` = 40001 order by value desc) t order by rank;
Query OK, 0 rows affected (0.000 sec)

+------+-----------+-------+
| rank | player_id | value |
+------+-----------+-------+
|    1 |         1 |     0 |
|    2 |         8 |     0 |
|    3 |         9 |     3 |
|    4 |        10 |   333 |
|    5 |        11 |    31 |
|    6 |        12 |     0 |
|    7 |        13 |     0 |
|    8 |        14 |     0 |
|    9 |        15 |    12 |
+------+-----------+-------+
Ad

Answer

Rank window function seems the obvious answer, btw partition by is optional

create table t
(player_id int, value int);
insert into t values
(        10 ,   333 ),
(        11 ,    31 ),
(        15 ,    12 ),
(         9 ,     3 ),
(         1 ,     0 ),
(         8 ,     0 ),
(        12 ,     0 ),
(        13 ,     0 ),
(        14 ,     0 );

select *,
        rank() over (order by value desc)
from t

+-----------+-------+-----------------------------------+
| player_id | value | rank() over (order by value desc) |
+-----------+-------+-----------------------------------+
|        10 |   333 |                                 1 |
|        11 |    31 |                                 2 |
|        15 |    12 |                                 3 |
|         9 |     3 |                                 4 |
|        13 |     0 |                                 5 |
|        14 |     0 |                                 5 |
|         1 |     0 |                                 5 |
|         8 |     0 |                                 5 |
|        12 |     0 |                                 5 |
+-----------+-------+-----------------------------------+
9 rows in set (0.001 sec)

and if you only want for 11 put code in cte

with cte as
(select *,
        rank() over (order by value desc) rnk
from t
)
select player_id, rnk
from cte 
where player_id = 11;
Ad
source: stackoverflow.com
Ad