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

Question

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)

Answer 1

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