Solved

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

Posted on 2008-10-02
2
312 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Format data and display in formatted manner 3 55
mySql Syntax 7 44
how do i form the query to get different columns total count 13 42
updating the date data 12 20
More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
Creating and Managing Databases with phpMyAdmin in cPanel.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
A short film showing how OnPage and Connectwise integration works.

914 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now