From: Peter Brawley
Date: November 13 2006 2:18pm
Subject: Re: How do I do this query efficiently?
List-Archive: http://lists.mysql.com/mysql/203310
Message-Id: <45587EC9.80807@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

 >for every "id" I want exactly one row, namely the row with the maximum 
value of "version".

SELECT id,MAX(version) FROM data GROUP BY id;

PB

-----

Sebastiaan van Erk wrote:
> Hi all,
>
> I have the following simple table:
>
> CREATE TABLE data (
>   id int NOT NULL,
>   version int NOT NULL,
>   value int NOT NULL,
>
>  PRIMARY KEY (id, version)
> )
> ENGINE=InnoDB DEFAULT CHARSET=utf8;
>
> What I would like to do is to find all the values for the latest 
> versions, that is, for every "id" I want exactly one row, namely the 
> row with the maximum value of "version". Without using SQL I can think 
> of a very efficient way for the query optimizer to accomplish this for 
> me, namely by using the primary key to iterate through each value for 
> "id" and then looking for the maximum "version", which is essentialy 
> O(1) because it's the last entry in the index for that specific id. 
> However, I don't know how to do this query in SQL, the closest I got was:
>
> select id, version, value from data d1 where version = (select 
> max(d2.version) from data d2 where d2.id = d1.id)
>
> However this is extremely inefficient and takes much to long (in the 
> order of 5 seconds on the data I have).
>
> Does anybody know an efficient way to do this?
>
> Thanks in advance,
> Sebastiaan
>
>
>
>


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.409 / Virus Database: 268.14.3/530 - Release Date: 11/11/2006