0

Anyone know how I can get the date that corresponds to the maximum score per game_id, per level please:

----------------------------------------
|id |game_id |level |score |date       |
----------------------------------------
|1  |0       |1     |90    |1391989720 |
|1  |0       |1     |95    |1391989721 |
|1  |1       |1     |4     |1391989722 |
|1  |1       |1     |8     |1391989723 |
|1  |1       |2     |6     |1391989724 |
----------------------------------------

    SELECT cdu_user_progress.game_id, 
           cdu_user_progress.level, 
           max_score, 
           cdu_user_progress.date 
    FROM 
    (
        SELECT game_id, level, MAX(score) AS max_score
        FROM cdu_user_progress 
        GROUP BY game_id, level 
    ) AS ms 
    JOIN cdu_user_progress ON cdu_user_progress.game_id = ms.game_id AND 
                              cdu_user_progress.level = ms.level AND 
                              cdu_user_progress.score = ms.max_score

Now what if I ALSO want to get the score for the FIRST game_id (per level) and it's date, to get this output:

-----------------------------------------------------------------
|game_id |level |max_score |max_date   |first_score |first_date |
-----------------------------------------------------------------
|0       |1     |95        |1391989721 |90          |1391989720 |
|1       |1     |8         |1391989723 |4           |1391989722 | 
|1       |2     |6         |1391989724 |6           |1391989724 |
-----------------------------------------------------------------
Dharman
  • 30,962
  • 25
  • 85
  • 135
user3241112
  • 69
  • 3
  • 8
  • 1
    Luckily, this question is asked every. single. day. in SO. Sometimes it's answered correctly too! – Strawberry Feb 10 '14 at 14:20
  • 1
    You asked 15 questions so far. Ever noted the "Related" column popping up when you write up your question? The duplicate is also picked from the "Related" column on the right to your question. – fancyPants Feb 10 '14 at 14:23

1 Answers1

0

Because it's mysql, you can simply do this to pull the whole row with the highest score per game:

select * from (
  select *
  from cdu_user_progress
  order by score desc) x
group by game_id

This works because of mysql non-standard group by functionality, whereby it returns the first row encountered for each group when not all the non-aggregate columns are named in the group by clause.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • 1
    This is definitely not guaranteed to work. MySQL documentation is quite explicit that when using the group-by extension that allows non-aggregated columns in the select, then the values come from *arbitrary* ("indeterminate") rows and not necessarily from the first row. The documentation is here: http://dev.mysql.com/doc/refman/5.7/en/group-by-extensions.html. – Gordon Linoff Feb 10 '14 at 14:28
  • @GordonLinoff heard it all before... yawn. Show me a test case that returns anything other than the first row. You can't, because it **never** happens. This technique works 100% or the time. The only way it will change is if there's a major update to mysql and they decide to change the implementation of this functionality, which is pretty unlikely. Other than that, trust me... you can rely of this behaviour. – Bohemian Feb 10 '14 at 14:36
  • 1
    . . If the documentation didn't explicitly point this out, then I would just -- with a bit of pain -- ignore the issue (I'm pretty sure the ANSI standard says that `order by` is ignored in subqueries except under some very specific circumstances). So although this works in practice, I think it is very bad practice to promulgate. Worse, for instance, than using `group by` to order things. The latter is explicitly documented to work and will be deprecated. The former is explicitly documented not to work. – Gordon Linoff Feb 10 '14 at 14:45
  • @GordonLinoff I sleep just fine :) – Bohemian Feb 10 '14 at 14:58