Solved

MySQL: Most Recent Item From Category

Posted on 2009-04-13
1
301 Views
Last Modified: 2012-06-27
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
Comment
Question by:headzoo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 14

Accepted Solution

by:
racek earned 500 total points
ID: 24132539
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

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question