Mohamad Mohamad - 1 month ago 13
MySQL Question

SQL : how to select the last inserted record from a table joined to another


-Fields of table "news" :
id,title,category


note: the field "category" is a foreign key related to table category

-Fields of table "category" :
id,title



I need to join "news" with "category" and get the last inserted news of each category

example:

table news

id-----title-----category
1-----title1-----3
2-----title2-----3
3-----title3-----5
4-----title4-----5


table category

id-----title
3------cat3
5------cat5


result should be:

id-----title-----category
2-----title2-----cat3
4-----title4-----cat5

Answer

If you assume that the id will always increment with a new item then this query should be safe. That then relies upon the field to perform a function for which it was not intended, and if there was an insert_date field that would probably be superior.

Select news.id, news.title, category.title as category
  From news
  Inner Join (Select Max(id) as id From news Group By category) As latest 
     On latest.id = news.id
  Inner Join category On news.category = category.id;