Arshdeep Arshdeep - 5 months ago 16
PHP Question

Please assist me in building this query

I want to extract rows of group 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;


alt text




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


alt text

Answer
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;