MYSQL: Select most recent row with 2 or more columns distinct

Question

I am working with a system that stores students' responses to questions in a table by creating a new entry each time the student resubmits his/her answer. I only want the final answer though. I have looked at some of the greatest-n-per-group posts on here and have attempted to apply some of those queries, but they haven't yielded the results I was hoping for yet. The closest that I have gotten was getting the most recent answer to the most recent question, but I need answers to every question that the student has responded to. Below is the closest that I have gotten so far.

select t1.* from response_history as t1
LEFT OUTER JOIN response_history as t2
    ON t1.student_id = t2.student_id
        AND (t1.`date` > t2.`date`
            OR (t1.`date` = t2.`date` AND t1.id > t2.id))
where t2.student_id IS NULL;

score 0 · Answer 1 · edited May 23 '17 at 10:28

0

After some more trial and error and taking a peek at this answer, I was able to come up with this query that seems to work correctly.

SELECT t1.* FROM response_history t1
INNER JOIN (SELECT t2.student_id, t2.session_question_id, MAX(`date`) as `date`
        FROM response_history t2
        group by session_question_id, student_id) as groupedt2
ON t1.student_id = groupedt2.student_id and t1.`date` = groupedt2.`date`

edited May 23 '17 at 10:28

Community

1
1

answered May 31 '16 at 21:22

vdwtanner

197
2
14

You also need `t1.session_question_id = groupedt2.session_question_id`. – Barmar May 31 '16 at 21:25
Ok, thanks for the help! I've selected your answer as correct. – vdwtanner Jun 01 '16 at 18:19

score 0 · Accepted Answer · edited May 23 '17 at 10:28

This is just like most of the answers in SQL Select only rows with Max Value on a Column, you just have to group by and join on both the student ID and question ID.

SELECT t1.*
FROM response_history AS t1
JOIN (SELECT student_id, session_question_id, MAX(date) AS date
      FROM response_history
      GROUP BY student_id, session_question_id) AS t2
ON t1.student_id = t2.student_id 
    AND t1.session_question_id = t2.session_question_id
    AND t1.date = t2.date

MYSQL: Select most recent row with 2 or more columns distinct

2 Answers2