List:General Discussion« Previous MessageNext Message »
From:Sebastiaan van Erk Date:November 13 2006 4:21pm
Subject:Re: How do I do this query efficiently?
View as plain text  
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
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
Thread
How do I do this query efficiently?Sebastiaan van Erk13 Nov
  • Re: How do I do this query efficiently?Peter Brawley13 Nov
    • Re: How do I do this query efficiently?Rolando Edwards13 Nov
      • Re: How do I do this query efficiently?Rolando Edwards13 Nov
      • Re: How do I do this query efficiently?Sebastiaan van Erk13 Nov
    • Re: How do I do this query efficiently?Sebastiaan van Erk13 Nov
      • Re: How do I do this query efficiently?Peter Brawley13 Nov
        • Re: How do I do this query efficiently?Rolando Edwards13 Nov
        • Re: How do I do this query efficiently?Sebastiaan van Erk13 Nov
Re: How do I do this query efficiently?Peter Brawley16 Nov
  • Re: How do I do this query efficiently?Manuel Vacelet17 Nov