Pretty slick. ----- Original Message ----- From: Peter Brawley <[EMAIL PROTECTED]> To: Sebastiaan van Erk <[EMAIL PROTECTED]> Cc: mysql@lists.mysql.com Sent: Monday, November 13, 2006 10:43:26 AM GMT-0500 US/Eastern Subject: Re: How do I do this query efficiently?
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 -- 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]
