From: Peter Brawley
Date: July 24 2008 3:32pm
Subject: Re: how to optimize: max(timetstamp) where a.foo=b.foo ?
List-Archive: http://lists.mysql.com/mysql/213835
Message-Id: <4888A088.5060905@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

>to get the latest value for each id i have queries like:

>select * from tab A where timestamp = (select max(timestamp) from tab B where B.id=A.id) group by id ;

See "Within-group aggregates" at 
http://www.artfulsoftware.com/infotree/queries.php.

PB

walter harms wrote:
> hi list,
> i have tables that look like this( <10.000 entries) :
>
> id,
> timestamp,
> value
>
>
> to get the latest value for each id i have queries like:
>
> select * from tab A where timestamp = (select max(timestamp) from tab B where B.id=A.id) group by id ;
>
> on a fast system it takes round 4 sec to complet, but since this is done requlary it slows down to much.
> the same query takes on a informix system an a slower computer next to nothing.
>
> any ideas ?
>
> re,
>  walter
>
>
>
>