Solved

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

Posted on 2008-10-02
2
318 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
[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
2 Comments
 
LVL 25

Accepted Solution

by:
Tomas Helgi Johannsson earned 500 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

Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

717 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