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