CODEWITHSUNDEEP
mysqlsqlsubquery
luzny
luzny

Reputation: 2400

How to make SQL query ranking?

How to get the ranking best of the best ever, considering the best time and the score? Assuming that user won a few times, how to count how many times best user wins and what is the average time of this wins?

+----------------+----------------+--------+-----------------------+
| user_id        | quiz_id        | score  | finish                |
+----------------+----------------+--------+-----------------------+
| 1              | 1              | 1      | 2011-05-18 21:39:00   |
| 2              | 1              | 1      | 2011-05-18 21:43:10   |
| 3              | 1              | 0      | 2011-05-18 21:40:55   |
| 1              | 2              | 1      | 2011-05-18 22:51:57   |
| 2              | 2              | 1      | 2011-05-18 22:21:37   |
| 3              | 2              | 0      | 2011-05-18 22:22:48   |
| 4              | 2              | 1      | 2011-05-18 22:58:14   |              
+----------------+----------------+--------+-----------------------+

Upvotes: 0

Views: 144

Answers (1)

Jim Rubenstein
Jim Rubenstein

Reputation: 6920

To get the best score considering the latest finish datetime is pretty easy.

Overall

SELECT user_id, quiz_id, score, finish
FROM table
ORDER BY score DESC, finish DESC
LIMIT 1

For Each Quiz

SELECT user_id, quiz_id, score, finish
FROM table
GROUP BY quiz_id
ORDER BY score DESC, finish DESC

For Each User

SELECT user_id, quiz_id, score, finish
FROM table
GROUP BY user_id
ORDER BY score DESC, finish DESC

I'm not sure what you mean by number of user wins. What signifies a win? Same with average time; there's not enough information here to help you with these two questions.

Upvotes: 1

Related Questions