Improve company productivity with a Business Account.Sign Up

x
?
Solved

Mysql: Extract categories and the latest article in each category.

Posted on 2008-10-02
2
Medium Priority
?
352 Views
Last Modified: 2012-05-05
Hi,

I have 2 tables:
- news: contains all the articles
- cat news: contains the categories of the articles.

I would like to extract all the the categories and the latest article in each category.
Basically, what I want is to extract all the latest articles grouped by its relative category.  (each article is asigned to only 1 category).

This is the set of data I want to extract:
id_cat_news      |      cat_news_nome      |      id_news      |      news_titolo      |      news_data_mod

This is the query I'm using:
SELECT cat_news.id_cat_news,cat_news.cat_news_nome,news.id_news,news.news_titolo,news.news_data_mod
FROM news
LEFT JOIN cat_news on news.id_cat_news=cat_news.id_cat_news
GROUP BY cat_news.id_cat_news
ORDER BY news.news_data_mod desc

It extracts all the data I want but it doesn't extract the latest article in each category.

How can I modify this query in order to extract also the latest articles in each category.

Please let me know.
-L.
0
Comment
Question by:Laurent Belin
2 Comments
 
LVL 26

Accepted Solution

by:
Tomas Helgi Johannsson earned 2000 total points
ID: 22622703
     Hi

This would work

Regards,
   Tomas Helgi

SELECT c.id_cat_news,c.cat_news_nome,n.id_news,n.news_titolo,n.news_data_mod
FROM news n, cat_news c
WHERE n.id_cat_news=c.id_cat_news
AND n.id_news = (select max(nn.id_news) from news nn where nn.id_cat_news = n.id_cat_news)
GROUP BY c.id_cat_news
ORDER BY n.news_data_mod desc

Open in new window

0
 

Author Closing Comment

by:Laurent Belin
ID: 31502309
Thank you!
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

606 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