List:General Discussion« Previous MessageNext Message »
From:Rick James Date:March 18 2013 6:00pm
Subject:RE: Retrieve most recent of multiple rows
View as plain text  
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: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

Thread
Aw: Re: Retrieve most recent of multiple rowsStefan Kuhn14 Mar
  • Re: Re: Retrieve most recent of multiple rowsAnanda Kumar14 Mar
    • Re: Retrieve most recent of multiple rowsJohan De Meersman14 Mar
      • Re: Retrieve most recent of multiple rowsMarcus Vinicius15 Mar
      • RE: Retrieve most recent of multiple rowsRick James18 Mar