SQL Query, Selecting 5 most recent in each group

Question

I have this table

CREATE TABLE `codes` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `language_id` int(11) unsigned NOT NULL,
 `title` varchar(60) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
 `time_posted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

language_id refers to what language the record is in. What I would like to do is retrieve a list of the five most recent (ORDER BY time_posted DESC LIMIT 5) records in each language_id. I could do this in a loop within PHP with a number of different SQL queries but I feel there is a simpler way.

I've got to get a book on SQL, haha.

Thanks.

Answer 1

Here's how I solve this "top N per group" type of query in MySQL:

SELECT c1.*
FROM codes c1
LEFT OUTER JOIN codes c2
  ON (c1.language_id = c2.language_id AND c1.time_posted < c2.time_posted)
GROUP BY c1.id
HAVING COUNT(*) < 5;

See also "How do I select multiple items from each group in a mysql query?"