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
ORDER BY score DESC LIMIT 5;
nickname - score:
zod - 30
ciao - 20
jiji - 20
mayina - 20
jon - 0.
SELECT COUNT(*) AS rank
WHERE score >= (SELECT score FROM player WHERE nickname = 'jiji')
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
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');