CODEWITHSUNDEEP
phpmysqlgroup-byjoingreatest-n-per-group
Arshdeep
Arshdeep

Reputation: 4323

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

Upvotes: 1

Views: 145

Answers (4)

Chris Forrette
Chris Forrette

Reputation: 3214

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

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562621

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;

Upvotes: 2

OMG Ponies
OMG Ponies

Reputation: 332661

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.

Upvotes: 3

Kalium
Kalium

Reputation: 4682

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.

Upvotes: -2

Related Questions