Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 309
  • Last Modified:

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.
0
headzoo
Asked:
headzoo
1 Solution
 
racekCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now