Rank an SQL table with MYSQL, ranked by score, WHERE game = x

Question

I'm trying to build a really simple online functionality system. One of the queries that this system must handle is the returning of a scoreboard (all of the players scores, ordered and ranked). I know basic sql queries, but i'm completely lost when it comes to sub queries and variables within queries etc.

The table only has three columns. game, user_id, score. The table will let people upload and download scores for any game. I need to work out how to create a query that returns only the users from the game being queried, orders the players by score, then ranks them so duplicate scores will have the same rank. Here's a brief example of the desired outcome:

TABLE

user       game       score

fred       A           100         
bill       A           78          
john       A           78          
dave       B           71          
terry      B           60          
jean       B           60          
tom        A           60 
nick       A           57 

DESIRED OUTPUT

user      score       rank

fred       100         1
bill       78          2
john       78          2
tom        60          4
nick       57          5

CURRENT OUTPUT ** TAKES INTO ACCOUNT THE GAMES IT SHOULD IGNORE

user      score       rank

fred       100         1
bill       78          2
john       78          2
tom        60          5
nick       57          8

This is currently the query that works the best:

SELECT @rank:=@rank+1 AS ranking, user_id, score
FROM score_table , (SELECT @rank:=1) AS i
WHERE game='A' 
ORDER BY score DESC

But the rank seems to take into account other games, which ruins the rankings. Other queries i've found have ranked correctly but not eliminated the other games (again, taking the other games scores into account when ranking.

Again, the above is an example I tweaked, as I have no idea how to use the @ variables, sub queries etc.

Many thanks,

• Dan.

Answer 1

To show the same rank for same score you can use case and additional variable for checking same rank

SELECT ranking,user,score FROM
(SELECT 
@rank:=case when score =@s then @rank else @rank+1 end AS ranking,
@s:=score,
user, score,game
FROM tablename , (SELECT @rank:=0,@s:=0) AS i
ORDER BY score DESC
) new_alias
WHERE game='A'

Demo

Edit from comments

Updated Demo