Solved

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

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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

739 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