Solved

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

Posted on 2008-10-02
2
315 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:lollodev
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:lollodev
ID: 31502309
Thank you!
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

809 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