Is there one query way to get user's position and score from table with 2 fileds as primary key.

Primary key is User Arena.

The way I insert new or update existing scores in table:

INSERT INTO `ArenaScores` (User, Arena) VALUES ([USER ID], [ARENA ID]) ON DUPLICATE KEY UPDATE `Score`=`Score` 1

What I tried is this:

SELECT COUNT(*) AS `Rank` 
FROM `ArenaScores` 
WHERE `Score`>=(SELECT `Score` FROM `ArenaScores` WHERE User=3 AND Arena=15) AND Arena=15

Problem with my query is that it does not account for nulls and it's unable to show User Score.

What am I trying to achieve when there is results for user:

And if there is no results:

Also, I'm open for better solutions.

CodePudding user response：

Based on what you tried to achieve, I suggest this query:

select 
  1 as User, 
  15 as Arena, 
  sum(r.Score is not null)   1 as `Rank`, 
  u.score as Score
from 
  ArenaScores as u 
  left join ArenaScores as r on 
    u.Arena = r.Arena 
    and r.score > u.Score 
where 
  u.User = 1 
  and u.Arena = 15;

The idea is to join the table on itself and count how many rows have a greater score. Which gives the rank when we add 1 to this result. Instead of counting the number of rows that have a score "greater than or equal to", which would produce bad results in case of equal scores. (If the 3 higher scores for an arena are 5000, a user that gets this score should be ranked 1, and not 3).

Also as User and Arena are provided to the query, we can use them also in the select statements to manage the case when the User doesn't have any score for a given Arena.

Results samples for users 1 and 4:

mysql> select 1 as User, 15 as Arena, sum(r.Score is not null)   1 as `Rank`, u.score as Score from ArenaScores as u left join ArenaScores as r on u.Arena = r.Arena and r.score > u.Score where u.User = 1 and u.Arena = 15;
 ------ ------- ------ ------- 
| User | Arena | Rank | Score |
 ------ ------- ------ ------- 
|    1 |    15 |    2 |  1000 |
 ------ ------- ------ ------- 
1 row in set (0.00 sec)

mysql> select 4 as User, 15 as Arena, sum(r.Score is not null)   1 as `Rank`, u.score as Score from ArenaScores as u left join ArenaScores as r on u.Arena = r.Arena and r.score > u.Score where u.User = 4 and u.Arena = 15;
 ------ ------- ------ ------- 
| User | Arena | Rank | Score |
 ------ ------- ------ ------- 
|    4 |    15 |    3 |   750 |
 ------ ------- ------ ------- 
1 row in set (0.01 sec)

mysql> select 1 as User, 16 as Arena, sum(r.Score is not null)   1 as `Rank`, u.score as Score from ArenaScores as u left join ArenaScores as r on u.Arena = r.Arena and r.score > u.Score where u.User = 1 and u.Arena = 16;
 ------ ------- ------ ------- 
| User | Arena | Rank | Score |
 ------ ------- ------ ------- 
|    1 |    16 |    1 |  2000 |
 ------ ------- ------ ------- 
1 row in set (0.00 sec)

mysql> select 4 as User, 16 as Arena, sum(r.Score is not null)   1 as `Rank`, u.score as Score from ArenaScores as u left join ArenaScores as r on u.Arena = r.Arena and r.score > u.Score where u.User = 4 and u.Arena = 16;
 ------ ------- ------ ------- 
| User | Arena | Rank | Score |
 ------ ------- ------ ------- 
|    4 |    16 | NULL |  NULL |
 ------ ------- ------ ------- 
1 row in set (0.00 sec)