help me build this query

Question

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 dont want to use subqeuries .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

Answer 1

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.

Answer 2

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.

Answer 3

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;

Answer 4

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