Wow, neat. I didn't think you could do that without a subquery somewhere.
Learned a cool new trick today. Thanks!
Regards,
Sebastiaan
Peter Brawley wrote:
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
