List:General Discussion« Previous MessageNext Message »
From:hsv Date:March 16 2013 1:11am
Subject:Re: Retrieve most recent of multiple rows
View as plain text  
>>>> 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.

Thread
Retrieve most recent of multiple rowsNorah Jones13 Mar
  • Re: Retrieve most recent of multiple rowsAnanda Kumar13 Mar
  • Re: Retrieve most recent of multiple rowsJohan De Meersman13 Mar
    • Re: Retrieve most recent of multiple rowsAnanda Kumar13 Mar
      • Re: Retrieve most recent of multiple rowsAnanda Kumar13 Mar
        • Re: Retrieve most recent of multiple rowsJohan De Meersman13 Mar
          • Re: Retrieve most recent of multiple rowsAnanda Kumar14 Mar
  • Re: Retrieve most recent of multiple rowshsv16 Mar