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;
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,
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.409 / Virus Database: 268.14.3/530 - Release Date: 11/11/2006
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=1