Link to home
Start Free TrialLog in
Avatar of freestate
freestate

asked on

Selecting the latest answers only per user from survey DB

I have a database table that contains answers from a survey, stored on a per user database with the following columns:

ID
User
Question
Answer
Time

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
FROM answers
WHERE user =8
GROUP BY question

Can someone help me?
Avatar of Om Prakash
Om Prakash
Flag of India image

Try:

SELECT
      MAX(a2.time),
      question,
      answer,
      user
FROM answers a1
GROUP BY user, question , answer
Your first query will return every row for user 8 as you are matching id inside the subquery. i believe you want to do this
SELECT time, question, answer, user FROM answers a1 WHERE user = 8 AND time=(SELECT MAX(a2.time) FROM answers a2 where a1.question=a2.question and user = 8)

The second query returns the desired result i think.
SELECT MAX( time ) , question, answer, user
FROM answers
WHERE user =8
GROUP BY question;

The output i receive on test data is the latest answer for distinct questions for user 8
SOLUTION
Avatar of JoeNuvo
JoeNuvo
Flag of Viet Nam image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of cyberkiwi
[
SELECT MAX( time ) , question, answer, user
FROM answers
WHERE user =8
GROUP BY question
]
does not work because answer and user are neither in GROUP BY nor aggregates. Any random answer and user will be returned, not the ones matching MAX(time)

I believe this is what you are after.

SELECT time, question, answer, user
FROM answers
where ID in
(
      select max(id) from answers
      group by user, question, answer
)
Avatar of freestate
freestate

ASKER

Doh  -you're right, the second one works.  Thanks for explaining why, because I was just shooting in the dark to get this to work.  
>> The output i receive on test data is the latest answer for distinct questions for user 8

The accepted answer is not correct.  It doesn't always work.
See http:#a33645080

http://dev.mysql.com/tech-resources/articles/debunking-group-by-myths.html
http://blog.mclaughlinsoftware.com/2010/03/10/mysql-standard-group-by/
Cyberwiki - you're right - how can I reopen the question?

Your solution seems almost there, but I get duplicate answers where a different answer has been provided to the same question at different times.

Am I right in thinking that this solves this problem (it seems to):

SELECT time, question, answer, user
FROM answers
where ID in
(
      select max(id) from answers
      group by user, question
)

(i.e. drop 'answers' from the group by clause)
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
The below query makes no sense in selecting the latest time max(id).. You are selecting the latest entry by ID not by time. I have inserted few sample data in the structure provided by you. It works fine if i enter the data in sequence. But it breaks when i update the Time and make any random entry latest by time.

SELECT time, question, answer, user
FROM answers
where ID in
(
      select max(id) from answers
      group by user, question
)
Fair point - although for my purposes, entries are stored sequentially with an auto-generated incrementing ID, so it suits purpose.