2

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.

sebkrueger
  • 386
  • 5
  • 16
Juls A
  • 101
  • 1
  • 8

2 Answers2

3

Try this:

SET @rank=0;
SELECT * 
FROM (SELECT @rank:=@rank+1, nickname, score 
    FROM player 
    ORDER BY score 
    DESC) AS t
WHERE t.nickname = 'jiji';

Correct comment about this not being stable in case of score ties. To make it stable, we can change the sorting to be based on score and then nickname:

SELECT * 
FROM (SELECT @rank:=@rank+1, nickname, score 
    FROM player 
    ORDER BY score, nickname 
    DESC) AS t
WHERE t.nickname = 'jiji';
Galz
  • 6,713
  • 4
  • 33
  • 39
  • I just came up with the same query before checking on here again and this works (thanks!) But now as I'm using this on php, I have to make two separate queries, one for the `set @rank=0` and the other for the rest. And trying to do so with the second query it tells me: "Error. Unknown column 'rank' in 'field list' – Juls A Nov 02 '16 at 19:19
  • 1
    The code I'm using: `$querysetRank = "set @rank = 0"; if ($result = mysqli_query($database, $querysetRank)) { $queryPlayerRank = "SELECT rank FROM (SELECT @rank:=rank+1 as rank, nickname FROM player order by score desc) as pl where pl.nickname = '$nickname'"; if (!($resultZ = mysqli_query($database, $queryPlayerRank))) //error else //do things` – Juls A Nov 02 '16 at 19:22
  • @JulsA I think you need to make sure both queries run from the same session, so `@rank` is available in the second query. – Galz Nov 02 '16 at 19:22
  • @JulsA - I am not a php expert. This may require another question (or there may be existing questions you can find( – Galz Nov 02 '16 at 19:23
  • As a note: This answer is not *stable*, because sorting in relational databases in not stable. That means that when there are ties, the rank for a given row could vary between runs of the query. – Gordon Linoff Nov 02 '16 at 19:29
  • @GordonLinoff - Correct. Edited – Galz Nov 02 '16 at 19:36
  • How to use `SET @rank=0;` in PHP – Iman Marashi Apr 24 '18 at 22:30
2

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');
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • This actually is what I was trying to do (thank you) but I just noticed that phpmyadmin doesn't order also per alphabetical order of the key (nickname) when I do the first query. So.. How can I get this `SELECT nickname, score FROM player ORDER BY score DESC LIMIT 5` query to also order by the nickname alphabetical order? I tried something but nothing worked. (I'm a bit confused 'cause it's been hours since I'm trying to get over this, sorry..) – Juls A Nov 02 '16 at 19:56