>>>> 2013/03/13 13:18 +0000, Norah Jones >>>>
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.
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 1363091025 <<<< changed!
<<<<<<<<
Something like this:
select * from x where (answer_timestamp,q_id) in (
select max(answer_timestamp), q_id from x group by q_id)
group by q_id;
It makes use of MySQL s feature of allowing not aggregated fields with GROUP
BY. Otherwise each record with the same "q_id" and greatest "answer_timestamp"
would be shown. Nothing is guaranteed which is indeed shown.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
