Problems - Group/Join - Wish to view most recent...

[Patrick Mann]

Hi,

I've been trying to sort this out for over 2 hours and I can't get it to work! What I want is for the code at the base of this post to list each of the forums with the most recent topic details. My problem is that it doesn't choose the most recent post, it chooses the first.

I've tried:
- changing 'forums_topics.topic_time' to 'MAX(forums_topics.topic_time)' [returns the MAX value, but not the corresponding topic_id/title - it still returns the first topic_id/title!]
- adding 'forums_topics.topic_time DESC' to 'ORDER BY' [no effect]
- adding 'forums_topics.topic_time' to 'GROUP BY' [this lists them successfully, and with this the ORDER BY has an effect, but I don't want to list all topics, just the most recent]
- adding subqueries (couldn't get to work)
- adding 'HAVING topic_time = MAX(forums_topics.topic_time)' after 'GROUP BY forums_forums.forum_id' [which returns only the forums with a single post]

Am I being incredibly stupid? Any help much appreciated :)

PMann


SELECT
forums_forums.forum_id AS forum_id,
forums_forums.forum_name,
forums_topics.topic_id,
forums_topics.topic_time AS topic_time,
forums_topics.topic_title,
forums_topics.topic_replies,
forums_users.user_id,
forums_users.username,
COUNT(*) AS total_articles,
SUM(forums_topics.topic_replies) AS total_comments
FROM
forums_forums,
forums_posts_text,
forums_topics,
forums_users
WHERE
forums_forums.cat_id = 1
AND forums_posts_text.post_id = forums_topics.topic_first_post_id
AND forums_topics.forum_id = forums_forums.forum_id
AND forums_users.user_id = forums_topics.topic_poster
GROUP BY
forums_forums.forum_id
ORDER BY
forums_topics.forum_id
ASC