Wow, neat. I didn't think you could do that without a subquery somewhere.
Learned a cool new trick today. Thanks!
Regards,
Sebastiaan
Peter Brawley wrote:
> Right, if you want the value column you need too, you need a different
> query ...
>
> SELECT t1.id, t1.version, t1.value
> FROM data t1
> LEFT JOIN data t2 ON t1.id=t2.id AND t1.version < t2.version
> WHERE t2.id IS NULL;
>
> PB
>
> Sebastiaan van Erk wrote:
>> 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
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
