List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:November 13 2006 3:43pm
Subject:Re: How do I do this query efficiently?
View as plain text  
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
>>>
>>>
>>>
>>>
>>
>>
>
>


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

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