Juls A Juls A - 1 month ago 9
MySQL Question

Select rank of a specific player sql query

I'm trying to make a query and I already searched for an answer on stackof but didn't find one matching my needs.
I have a table named player in which there are two columns, "nickname" and "score".
I use this query to get the top 5 players:

SELECT nickname, score
FROM player
ORDER BY score DESC LIMIT 5;


and I got this as the answer:

nickname - score:
zod - 30
ciao - 20
jiji - 20
mayina - 20
jon - 0.


Now, I'd like to have the rank of a single player, let's say "jiji" and get 3 as a result, because it's the third result in the list.

I tried many queries like

SELECT COUNT(*) AS rank
FROM player
WHERE score >= (SELECT score FROM player WHERE nickname = 'jiji')


but they always return 4 for "jiji" or "ciao", which is the rank of the last player who gets 20 as score in that table.

How can I get to have 3 for "jiji", instead? Thank you very much.

Answer

Using commonly used definitions, the rank for jiji would be:

SELECT count(*) + 1 AS rank 
FROM player
WHERE score > (SELECT score FROM player WHERE nickname = 'jiji');

This returns "2", because there are ties when score = 30.

If you want the rank to be stable and different for each row, you need an additional key. An obvious key (in this case) is nickname:

SELECT count(*)  AS rank 
FROM player p CROSS JOIN
     (SELECT score FROM player WHERE nickname = 'jiji') s
WHERE p.score > s.score or
      (p.score = s.score and p.nickname <= 'jiji');