MySql subqueries and max or group by? -


i have 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           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 write query this:

for each student in specific class select recent answer each question.

so, choosing class "1" return:

id student class question answer   time  2   1      1       1       d     12:36  3   1      1       2           12:38  5   2      1       1       c     11:26  6   2      1       3       d     11:35 

i've tried various combinations of subqueries, joins, , grouping, nothing working. ideas?

you can use sub-query recent answer per question, use derived table , join original table:

 select m.*  mytable m  inner join (     select student,  question, max(`time`) mtime     mytable     class = 1     group student, question  ) d on m.student = d.student , m.question = d.question , m.`time` = d.mtime m.class = 1 

demo here


Comments

Popular posts from this blog

python - pip install -U PySide error -

arrays - C++ error: a brace-enclosed initializer is not allowed here before ‘{’ token -

apache - setting document root in antoher partition on ubuntu -