MySql subqueries and max or group by?

Question

I have this table:

ID STUDENT CLASS QUESTION ANSWER   TIME
 1   1      1       1       c     12:30
 2   1      1       1       d     12:36
 3   1      1       2       a     12:38
 4   2      1       1       b     11:24
 5   2      1       1       c     11:26
 6   2      1       3       d     11:35
 7   2      3       3       b     11:24

I'm trying to write a query that does this:

For each STUDENT in a specific CLASS select the most recent ANSWER for each QUESTION.

So, choosing class "1" would return:

ID STUDENT CLASS QUESTION ANSWER   TIME
 2   1      1       1       d     12:36
 3   1      1       2       a     12:38
 5   2      1       1       c     11:26
 6   2      1       3       d     11:35

I've tried various combinations of subqueries, joins, and grouping, but nothing is working. Any ideas?

Answer 1

You can use a sub-query to get most recent ANSWER per QUESTION, Then use this as a derived table and join back to the original table:

 SELECT m.*
 FROM mytable AS m
 INNER JOIN (
    SELECT STUDENT,  QUESTION, MAX(`TIME`) AS mTime
    FROM mytable
    WHERE CLASS = 1
    GROUP BY STUDENT, QUESTION 
) AS d ON m.STUDENT = d.STUDENT AND m.QUESTION = d.QUESTION AND m.`TIME` = d.mTime
WHERE m.CLASS = 1

Demo here