Mysql: Extract categories and the latest article in each category.
Posted on 2008-10-02
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:
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.