MySQL: Most Recent Item From Category

I want to list all the post categories, while also showing the most recent post from that category. Can't figure this one out. Here are example tables:

CREATE TABLE `categories` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `posts` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(50) NOT NULL,
  `body` text NOT NULL,
  `date_created` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `posts_to_categories` (
  `post_id` int(10) unsigned NOT NULL,
  `category_id` int(10) unsigned NOT NULL
) ENGINE=InnoDB;

And some sample data:

INSERT INTO `categories` (`id`, `name`) VALUES
(1, 'Vacation'),
(2, 'Family');


INSERT INTO `posts` (`id`, `title`, `body`, `date_created`) VALUES
(1, 'Paris', 'Here are some pictures', '2009-04-11 15:45:39'),
(2, 'Spain', 'What a great trip!', '2009-04-12 15:45:52'),
(3, 'The kids', 'The latest pictures', '2009-04-11 15:46:26'),
(4, 'Mom & Dad', 'Enjoying mom''s birthday', '2009-04-12 15:46:44');


INSERT INTO `posts_to_categories` (`post_id`, `category_id`) VALUES
(1, 1),
(2, 1),
(3, 2),
(4, 2);

The query would produce results like this:

1) Vacation
- What a great trip!

2) Family
- Enjoying mom's birthday

That is, each category, along with the category's most recent post.
headzooAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
racekConnect With a Mentor Commented:
SELECT name, title
FROM  categories c, posts p,
(SELECT  category_id, MAX(post_id) pst_id
from posts_to_categories group by 1) x
WHERE c.id = x.category_id and p.id = x.pst_id;
0
All Courses

From novice to tech pro — start learning today.