Re: How do I do this query efficiently?

[Sebastiaan van Erk]

Hi,

Thanks for your quick answer, but unfortunately this query does not 
return the "value" column of the row; and that is the column I am 
ultimately interested in (in combination with the id).

Regards,
Sebastiaan

Peter Brawley wrote:
> >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
>>
>>
>>
>>
>
>