How to get the order of a row from sql query

Question

Sorry for the naive question. I can't tell my problem in a sentence rather I should explain that.

There is a table in mysql database say points. The structure is as following-

id  | user_id  |  quiz_id |  point  | time
----+----------+----------+---------+-------
  1 |    1     |    1     |   200   |  46
----+----------+----------+---------+-------
  2 |    2     |    1     |   120   |  35
----+----------+----------+---------+-------
  3 |    3     |    1     |   160   |  60
----+----------+----------+---------+-------
  4 |    1     |    2     |    50   |  60
----+----------+----------+---------+-------
  5 |    2     |    2     |   110   |  25
----+----------+----------+---------+-------
  6 |    3     |    2     |   150   |  38
----+----------+----------+---------+-------
  7 |    4     |    3     |   180   |  55
----+----------+----------+---------+-------
  8 |    2     |    4     |     0   |  34
----+----------+----------+---------+-------
  9 |    6     |    4     |    90   |  58
----+----------+----------+---------+-------
 10 |    3     |    5     |     0   |  60
----+----------+----------+---------+-------
 11 |    1     |    6     |   110   |  18
----+----------+----------+---------+-------
 12 |    8     |    6     |   150   |  23
----+----------+----------+---------+-------
 13 |    12    |    6     |   180   |  29
----+----------+----------+---------+-------

this is a quiz application where users are allowed to perform in quiz with 10 questions (20 per correct answer) within a total time of 60 seconds. If someone complete the quiz within the given time then the rest time would be summed up with the user point. If any user failed to answer any correct one then that user would get zero point for that quiz regardless the time taken.

Users can take multiple quiz and their points will be summed up in total.

I could manage up to this part. The sql query I used is as following-

SELECT 
    `user_id`, 
    sum( 
        case 
            when 60 >= `time` then `point` + (60-`time`) 
            else `point` 
        end
    ) as point 
FROM `points` group by `user_id` order by point DESC

The result I got-

  user_id  |  point
-----------+---------
    1      |   416
-----------+---------
    2      |   290
-----------+---------
    3      |   332 
-----------+---------
    4      |   185 
-----------+---------
    6      |    92
-----------+---------
    8      |   187 
-----------+---------
   12      |   211 
-----------+---------

Everything is working perfectly fine so far. Now I am stuck with getting the the rank of a specific user_id from the query above, like the order of the user_id 6 (which is 5 in order).

I am not requesting for a full working answer, just looking for a guide to the proper direction since I am unable to find it. In case any guide require server side programming, I user PHP for my app.

Any help is appreciated.

TIA

Update

In case someone needed the exact same solution, I am posting the working solution that I derived by slightly modifying the answer provided by @Gordon Linoff.

I wanted to get the rank of the user_id from the queried result above. Its obvious If someone viewed the leader board and he is not on the top list, he at least should see his rank.

SELECT Q.rank FROM(
    SELECT p.*, (@rn := @rn + 1) as rank
        FROM (SELECT `user_id`, 
             sum(case when 60 >= `time` then `answer`*20 + (60-`time`) 
                      else `answer`*20
                 end) as point 
         FROM `points` 
         GROUP BY `user_id` 
         ORDER BY point DESC
    ) p CROSS JOIN
    (SELECT @rn := 0) params ) AS Q WHEE Q.user_id= 6

The rank is retrieved for the user with id 6 and the result is like the following-

  rank
-------
   5

Thanks for the help.

This will help http://stackoverflow.com/questions/3333665/rank-function-in-mysql. Rank is available in MSSQL also. — Adish, Nov 15 '15 at 13:11

score 1 · Accepted Answer · answered Nov 15 '15 at 13:11

1

For ranking in MySQL, variables are probably the simplest approach. Here is how you would get the ranks for all users, assuming the point values are different:

SELECT p.*, (@rn := @rn + 1) as rank
FROM (SELECT `user_id`, 
             sum(case when 60 >= `time` then `point` + (60-`time`) 
                      else `point` 
                 end) as point 
     FROM `points` 
     GROUP BY `user_id` 
     ORDER BY point DESC
    ) p CROSS JOIN
    (SELECT @rn := 0) params;

answered Nov 15 '15 at 13:11

Gordon Linoff

1,242,037
58
646
786

Thank you very much for this quick and perfect answer. I made a little modification to the query you suggested to get the rank of the user_id. Thank you very much for the help. – maksbd19 Nov 15 '15 at 13:29

How to get the order of a row from sql query

1 Answers1