Mysql – thesql Quiz leaderboard filter by points, time taken

greatest-n-per-groupgroup byMySQLperformance

I have a quiz report table which shows a report for every quiz a user takes. I need to create a leaderboard from this, which shows the top users best score, filtering by points and then time taken.

here is a link to a sql fiddle http://sqlfiddle.com/#!2/65fbf0/1

I am really struggling as i need to filter the results by two columns for one user, my ideal result would be

Results for Quiz id 1
---------------------------------------------------------------   
| user_id  |  points  |  time_spend  |  start_dt  |  quiz_id  |
| 1        |  3       | 0.5          | May,15 2015|  1        |
| 2        |  3       | 0.8          | May,15 2015|  1        |
| 3        |  2       | 0.5          | May,15 2015|  1        |

Then a separate query for all quiz's showing the results from the last week.

Results from all Quizzs
---------------------------------------------------------------   
| user_id  |  points  |  time_spend  |  start_dt  |  quiz_id  |
| 1        |  3       | 0.5          | May,15 2015|  1        |
| 2        |  3       | 0.8          | May,13 2015|  3        |
| 3        |  2       | 0.5          | May,12 2015|  2        |

Best Answer

Solution to the query:

select *
from (
  select *
  from ProQzQuizReport
  order by points desc, time_spend asc) a
group by user_id;

http://sqlfiddle.com/#!2/65fbf0/9

Related Question