I want to extract rows of grou开发者_高级运维p by rls_id
but with latest/recent date
SELECT *
FROM `tbl_revisions`
WHERE `date` IN (SELECT MAX(`date`)
FROM `tbl_revisions`
GROUP BY `rls_id`)
GROUP BY `rls_id`
The above query works well but I don't want to use subqueries. I need some other way around.
CREATE TABLE IF NOT EXISTS `tbl_revisions`
(
`id` int(21) NOT NULL AUTO_INCREMENT,
`rls_id` int(21) NOT NULL,
`date` datetime NOT NULL,
`user` int(21) NOT NULL,
`data` blob NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=66 ;
Edit: Needs a faster way
Okay. i got 2 working queries thanks to both @Bill Karwin and @OMG Ponies .
I am pasting Explain for both queries here so other will learn better
Bill Karwin :
SELECT r1.*
FROM `tbl_revisions` r1
LEFT OUTER JOIN `tbl_revisions` r2
ON (r1.`rls_id` = r2.`rls_id` AND r1.`date` < r2.`date`)
WHERE r2.`rls_id` IS NULL;
OMG Ponies:
SELECT t.*
FROM TBL_REVISIONS t
JOIN (SELECT rls_id,
MAX(date) AS max_date
FROM TBL_REVISIONS
GROUP BY rls_id) x ON x.rls_id = t.rls_id
AND x.max_date = t.date
Without using subqueries? OK:
SELECT t.*
FROM TBL_REVISIONS t
JOIN (SELECT rls_id,
MAX(date) AS max_date
FROM TBL_REVISIONS
GROUP BY rls_id) x ON x.rls_id = t.rls_id
AND x.max_date = t.date
Some might call it a subselect, but x
is more accurately referred to as a derived table or inline view. Subselects are typically SELECT statements within the SELECT clause itself, like:
SELECT ...,
(SELECT COUNT(*)...)
Anyways, check the tag "greatest-n-per-group" for other various examples.
SELECT r1.*
FROM `tbl_revisions` r1
LEFT OUTER JOIN `tbl_revisions` r2
ON (r1.`rls_id` = r2.`rls_id` AND r1.`date` < r2.`date`)
WHERE r2.`rls_id` IS NULL;
This scenario is represented on the MySQL site as a 'groupwise max' problem. Looks like OMG Ponies got it right on -- you can't quite get away without any subqueries but OMG Ponies' JOIN-ed version uses an "uncorrelated" subquery, which is more efficient:
http://dev.mysql.com/doc/refman/5.1/en/example-maximum-column-group-row.html
Try this:
SELECT * FROM tbl_revisions WHERE date = MAX(date) FROM tbl_revisions GROUP BY rls_id;
I honestly haven't tried this, but give it a shot.
精彩评论