0

I am trying to make a leader board and use the following query:

SELECT d.*, 
       c.ranks 
FROM   (SELECT Max(correct_answer) AS correct_answer, 
               @rank := @rank + 1  Ranks 
        FROM   (SELECT Max(correct_answer) AS correct_answer 
                FROM   quiz_statuses 
                GROUP  BY user_id, 
                          level_no 
                ORDER  BY correct_answer DESC) t, 
               (SELECT @rank := 0) r) c 
       INNER JOIN quiz_statuses d 
               ON c.correct_answer = d.correct_answer

My table looks like:

id,user_id,level_no,correct_answer
1    1        1       5
2    1        1      10
3    2        1      36

The expected result should be as follows: 

id,user_id,level_no,correct_answer,ranks
3    2        1      36             1
2    1        1      10             2

But my query does not give me the result I want.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Infel
  • 1
  • 1
  • 1
    [MySQL 8.0 and above supports ranking functions](https://stackoverflow.com/questions/1895110/row-number-in-mysql/46753800#46753800) – Lukasz Szozda Mar 21 '20 at 11:58
  • If you're using this to output the results in PHP, you don't really need the query to return the rank as well, just order by score descending. The order will rank the users. – El_Vanja Mar 21 '20 at 12:34

0 Answers0