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:=@rank 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 |
------ ----------- -------
CodePudding user response：
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;