CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
user2015253
user2015253

Reputation: 1444

SQL: Take only the latest entry for each "group"?

Here I created some table to test with:

CREATE TABLE IF NOT EXISTS `test` (
  `index` varchar(255) NOT NULL,
  `index2` varchar(255) NOT NULL,
  `date` date NOT NULL,
  `somenumber` int(10) NOT NULL,
  PRIMARY KEY (`index`,`index2`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `test` (`index`, `index2`, `date`, `somenumber`) VALUES
('kevin', 'little', '2013-06-11', 1),
('kevin', 'little', '2013-07-03', 5),
('maria', 'smith', '2013-07-01', 3),
('martin', 'luther', '2013-07-04', 13),
('martin', 'luther', '2013-07-05', 14);

Now I want to get the latest somenumber for everyone, ordered by somenumber DESC. Here is my attempt:

SELECT * FROM `test` GROUP BY `index`, `index2` ORDER BY `somenumber` DESC

The problem is that this query does always take one somenumber for every group, but it's not always the latest.

(I know the indexnames don't make too much sense here, but I thought it would be an easier-to-read example than using random number-indexes)

Upvotes: 0

Views: 202

Answers (2)

Mikhail
Mikhail

Reputation: 1560

Try this:

SELECT t1.`INDEX`, t1.`INDEX2`, t1.`SOMENUMBER` FROM TEST t1 INNER JOIN
(SELECT `INDEX`, `INDEX2`, MAX(`DATE`) DATE FROM TEST
GROUP BY `INDEX`, `INDEX2`) t2 ON t1.INDEX = t2.INDEX AND t1.INDEX2 = t2.INDEX2 AND t1.DATE = t2.DATE

Upvotes: 2

agarici
agarici

Reputation: 612

Hope this will help you

SELECT * 
FROM  
    (SELECT * FROM `test`  ORDER BY `index`, `date` DESC) as temp
GROUP BY `index`, `index2` 
ORDER BY `somenumber` DESC

Upvotes: 3

Related Questions