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 <[EMAIL PROTECTED]> To: Sebastiaan van Erk <[EMAIL PROTECTED]>, mysql@lists.mysql.com 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 > > > > -- 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/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
