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).
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;
> 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,