List:General Discussion« Previous MessageNext Message »
From:Sebastiaan van Erk Date:November 13 2006 3:33pm
Subject:Re: How do I do this query efficiently?
View as plain text  
Hi,

Thanks for the response! I thought I had tried this, but maybe my index 
was wrong or my query was just different because when I tried it it was 
really slow (also around 5 seconds). However this does the trick and it 
is very fast (0.02 seconds). Thanks again!

Regards,
Sebastiaan

Rolando Edwards wrote:
> To get the value for each id's max version take the query
> Peter just suggested and make it a subselect and join it
> back to data like this:
>
> select data.* from
> (SELECT id,MAX(version) maxversion FROM data GROUP BY id) a,data b
> where a.id=b.id and a.version=b.maxversion;
>
> ----- Original Message -----
> From: Peter Brawley <peter.brawley@stripped>
> To: Sebastiaan van Erk <sebster@stripped>, mysql@stripped
> Sent: Monday, November 13, 2006 9:18:49 AM GMT-0500 US/Eastern
> Subject: Re: How do I do this query efficiently?
>
>  >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