SQL - how to extract LATEST news

[Cinquecento]

Mornin'

I've got a table that stores news articles - each belongs to a category and has a submission date. I want to find the latest article for each category. If I run the following query (linkTitle is the article name - pageTitle is the category - pageGroup denotes they are NEWS articles) it gives me the last entries in the table. Presumably it's because the query just pulls each result in sequence and the GROUP BY clause just squashes them together with the last in at the top. I can't put the 'order by pageAge' clause before 'Group by' so I can't get the last (by date) to show in the result correctly.

What's the solution?

select linkTitle,ownerID,pageTitle from page_content LEFT JOIN page_title ON ownerID=pageID WHERE pageGroup = 3 GROUP BY pageTitle order by pageAge;
+------------------+---------+-------------+
| linkTitle | ownerID | pageTitle |
+------------------+---------+-------------+
| recruitment news | 0005 | recruitment |
| asd | 0016 | industry |
| New react site | 0001 | react |
| tech news | 0018 | technology |
+------------------+---------+-------------+

(These are just the last entries for each category in the table - regardless of pageAge)

[Lars_Berg]

You need a correlated subquery

select * from page_content pc
where subMissionDate = 
(select max(submission_date)
  from page_content
 where page_title = pc.page_title)

If you are using Mysql you are out of luck as they don't support that type of querys. If you are stuck with Mysql you can do it in two steps by creating a temporary table with the contents of the subquery and use that in a join.

[Cinquecento]

Yes - mysql!

I fear it'll take too long to learn to do it your suggested way - I'll get PHP to distill the results, even if it is less desirable!

Cheers

Ian

[Cinquecento]

just worked it out.....

select max(pageAge),pageTitle from page_content LEFT JOIN page_title ON ownerID=pageID WHERE pageGroup = 3 GROUP by ownerID;

Cor - it's almost like I know what I'm doing sometimes!

Ian

[Lars_Berg]

No that's not correct.

What if you have the following data

pageAge! OwnerID ! pageTitle

20030112! 22........ ! Hej
20030113! 22........ ! Hop
20030114! 22........ ! ditt

With your query you never know which value of pageTitle you get. It can be any of the three.

All other DBMS would disallow that query.

[Cinquecento]

I thought that was right! Until I tested it - it's still just providing the last entries from each category!

as I said.... it's LIKE I know what I'm doing!

Ian