I know this has been covered somewhat but it hasn't helped my situation, rather there's been discrepancies in the rank. Wondering if anyone would be able to help!
So i have a game, the database tables are: users, maps, nicknames, user_game_scores
Im developing a leader board and easily able to get the information ordered by score. fantastic.
But i want to rank this so that i can pull a specific users scores and the rank be relevant to all scores. eg:
GLOBAL SCORES
user info - Score - (rank)1
user info - Score - (rank)2
user info - Score - (rank)3
etc.
Whereas USER SCORES are more likely to be:
user info - Score - (rank)82
user info - Score - (rank)94
user info - Score - (rank)115
etc.
I imagine the implementation to be this:
SELECT users.first_name, users.surname, player_nicknames.nickname, maps.map_name, user_game_scores.score,
FIND_IN_SET( score, ( SELECT GROUP_CONCAT( score ORDER BY score DESC ) FROM user_game_scores ) ) AS rank
FROM `user_game_scores`
INNER JOIN users ON user_game_scores.user_id = users.user_id
INNER JOIN maps ON user_game_scores.map_id = maps.map_id
INNER JOIN player_nicknames ON user_game_scores.user_id = player_nicknames.user_id
WHERE user_game_scores.deleted is null
AND users.deleted is null
AND player_nicknames.deleted is null
ORDER BY user_game_scores.score DESC
But it returns this: (click here) - names etc have been removed from the image as it may not be appropriate to display As you can see the Rank tends to miss a number or two (number 2 and 23). i understand that something like rank 24 will group and continue (which i prefer to happen in that instance) but i dont understand why some of the rank is missing and really dont want to post process this functionality.
Sorry this is long but i thought id provide as much information as i can. Thanks in advance!
It's probably because your SELECT GROUP_CONCAT subquery doesn't filter "deleted" (deleted is null) entries. – Paul Spiegel 9 hours ago
