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). select t.* 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:vegiv...@tuxera.be] > 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" <anan...@gmail.com> > > 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
