select * from tab where anwer_timestamp in (select max(anwer_timestamp) from tab where q_id in (select distinct q_id from tab) group by q_id);
That query will be extremely slow if you have lots of data. This is because the construct "in (select...)" is not optimized (until version 5.6).
from tab t
join ( select max(answer_timestamp) as ts from tab group by q_id ) x
on t.answer_timestamp = x.ts
That should almost work. It fails to do what you want if there are duplicate timestamps.
So, you make a second pass, this time taking the max(q_id) from each. (I'll leave that as an exercise for the student.)
> -----Original Message-----
> From: Johan De Meersman [mailto:vegivamp@stripped]
> Sent: Thursday, March 14, 2013 8:29 AM
> To: Ananda Kumar
> Cc: MySQL; Stefan Kuhn
> Subject: Re: Retrieve most recent of multiple rows
> ----- Original Message -----
> > From: "Ananda Kumar" <anandkl@stripped>
> > Subject: Re: Re: Retrieve most recent of multiple rows
> > select * from tab where anwer_timestamp in (select
> max(anwer_timestamp) from tab where q_id in (select distinct q_id from
> tab) group by q_id);
> This is entirely equivalent to
> select * from tab where anwer_timestamp in (select
> max(anwer_timestamp) from tab group by q_id);
> Additionally, there are no double timestamps in the dataset you used,
> whereas there are in OP's given dataset.
> It's also fairly hard to provide an accurate solution as long as OP
> hasn't clarified what exactly they want, really.
> Unhappiness is discouraged and will be corrected with kitten pictures.
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql