Selecting the latest answers only per user from survey DB
Posted on 2010-09-10
I have a database table that contains answers from a survey, stored on a per user database with the following columns:
where time stores the current timestamp when the question is answered.
As this survey can be completed more than once, I need to be able to return records on a per use basis that ONLY give the latest answer to each question. (I also need to return the 'earliest' at some point in the future, but I guess this will just require swapping Max for Min)
I've tried this, but it returns a list of all answers:
SELECT time, question, answer, user FROM answers a1 WHERE user = 8 AND time=(SELECT MAX(a2.time) FROM answers a2 WHERE a1.id = a2.id AND user = 8)
I also tried this, but it only seems to give the Max sate, not the associated answer:
SELECT MAX( time ) , question, answer, user
WHERE user =8
GROUP BY question
Can someone help me?