CODEWITHSUNDEEP
mysql
ojsglobal
ojsglobal

Reputation: 534

Retrieve most recent of multiple rows

Have asked this question several times, but never received an actual answer!

I have a table which looks like this:

answer_id  q_id  answer  qscore_id  answer_timestamp
1          10    Male    3          1363091016
2          10    Male    3          1363091017
3          11    Male    3          1363091018
4          10    Male    3          1363091019
5          11    Male    3          1363091020
6          12    Male    3          1363091020
7          11    Male    3          1363091025

So I have multiple answers for the same questions (q_id). I want to be able to retrieve only ONE answer per question and that be the most recent answer.

IE.

There should be THREE rows returned, which are all the most recent answered for that q_id:

4          10    Male    3          1363091019
6          12    Male    3          1363091020
7          11    Male    3          1363091020

Any help would be really appreciated.

Thanks

Upvotes: 0

Views: 45

Answers (2)

Kickstart
Kickstart

Reputation: 21513

Use a subselect to get the max timestamps and then join that back against the table

SELECT a.answer_id, a.q_id, a.answer, a.qscore_id, a.answer_timestamp
FROM SomeTable a
INNER JOIN (SELECT q_id, MAX(answer_timestamp) AS answer_timestamp FROM SomeTable GROUP BY q_id) Sub1
ON a.q_id = Sub1.q_id AND a.answer_timestamp = Sub1.answer_timestamp

Upvotes: 3

shola
shola

Reputation: 704

select * from table where q_id=? order by answer_timestamp desc limit 3

Upvotes: -2

Related Questions