mysql group by not returned in correct order

[Warboss Alex]

Hey guys, long time no post..

I've got a problem with a forums database right now, I'm trying to make a query that returns the 'forums index' page which shows the forum name, description and details of the last topic updated on that forum, total replies, total topics etc. everything is working fine except for one thing, I am not able to get the last topic updated. I am using GROUP BY and ORDER BY DESC to return a list of topics grouped by the forum they belong to, and selected based on which topic had the last post.

The query is:

SELECT posts.topic_id, topics.forum_id, posts.DATE AS maxdate, posts.user_id, posts.id AS post_id
FROM posts
INNER JOIN topics ON topics.id = posts.topic_id
WHERE posts.DATE = (
SELECT MAX( posts.DATE )
FROM posts
WHERE posts.topic_id = topics.id
)
GROUP BY topics.forum_id
ORDER BY posts.DATE DESC

If I remove the 'GROUP BY' clause then I get a whole list of topics (every topic in the database) but sorted in the right order (most recently updated one first). But I don't want this, I want the last topic updated per forum, so the GROUP BY clause is to only return one topic per forum - I had assumed the ORDER BY DESC clause would ensure that the one record the set is narrowed down to by the GROUP BY would be the last record but that's not the case.

Any help is gratefully appreciated!

[ZydoSEO]

Try this... Probably not the only way to do it but it appears to get back the most recent post for each topic:

SELECT p.Topic_ID, t.Forum_ID, p.Post_Date, p.ID as Post_ID
FROM posts p
JOIN topics t ON t.id = p.Topic_ID
WHERE p.Topic_ID IN (
SELECT p2.Topic_ID
FROM posts p2
WHERE p2.Topic_ID = t.ID
GROUP BY p2.Topic_ID
HAVING p.Post_Date = MAX(p2.Post_Date)
)

You can order the outer query however you like by adding another ORDER BY clause after the subquery. No need for an outer group by.

OOPS: Just noticed you wanted the most recent update in each forum. The above gives you the most recent update to each topic in each forum... Working on another query for you...

[edited by: ZydoSEO at 6:33 pm (utc) on Oct. 23, 2008]

[Warboss Alex]

Thanks, this fixed it:

SELECT posts.topic_id,
topics.forum_id,
posts.DATE AS maxdate,
posts.user_id,
posts.id AS post_id
FROM posts

INNER JOIN topics
ON topics.id = posts.topic_id

INNER JOIN
(SELECT topics.forum_id,
MAX(posts.DATE) as MD
FROM posts join topics
ON topics.id = posts.topic_id
GROUP BY topics.forum_id) dt
on posts.date = dt.md
and topics.forum_id = dt.forum_id
ORDER BY maxdate DESC

:) thanks for trying though!

edit: blah, how can I get the code to align properly?

[edited by: Warboss_Alex at 6:03 pm (utc) on Oct. 24, 2008]