Select forum topics with the most recent posts 1

[bitwise]

I'm implementing a basic forum. My database contains a FORUM table, a TOPICS table, and a POSTS table. Basically, a "forum" contains "topics" and a topic contains "posts". Pretty standard.

When I want to display a forum with all its topics I obviously want to order the topics by the one that had the most recent post. I have a query that looks like this:

SELECT FT.TopicID, FT.Subject, U.Username, DatePosted
FROM (tblForumTopics AS FT INNER JOIN tblForumPosts AS FP ON FT.TopicID=FP.TopicID)
INNER JOIN tblUsers AS U ON U.UserID=FP.UserID
WHERE FT.ForumID=8
ORDER BY FP.DatePosted DESC;

I have some tested data and it returns this:

[tt]
+---------+---------+-------------+---------------------+
| TopicID | Subject | Username | DatePosted |
+---------+---------+-------------+---------------------+
| 2 | Topic 2 | ~*melissa*~ | 2007-09-24 00:00:00 |
| 2 | Topic 2 | dummy | 2007-09-22 00:00:00 |
| 1 | Topic 1 | admin | 2007-09-20 00:00:00 |
| 2 | Topic 2 | admin | 2007-08-09 00:00:00 |
+---------+---------+-------------+---------------------+
4 rows in set (0.00 sec)
[/tt]

The problem is that it returns all the posts for a given topic. I want it to only return the newest distinct topic. The results I would like to see are would be this:
[tt]
+---------+---------+-------------+---------------------+
| TopicID | Subject | Username | DatePosted |
+---------+---------+-------------+---------------------+
| 2 | Topic 2 | ~*melissa*~ | 2007-09-24 00:00:00 |
| 1 | Topic 1 | admin | 2007-09-20 00:00:00 |
+---------+---------+-------------+---------------------+
[/tt]
See, distinct topics ordered by the last post. How can I limit my first result set to return the result set that I would like?

FYI, if I add a "GROUP BY FT.TopicID" just before the ORDER BY statement in my query I get these results:
[tt]
+---------+---------+----------+---------------------+
| TopicID | Subject | Username | DatePosted |
+---------+---------+----------+---------------------+
| 1 | Topic 1 | admin | 2007-09-20 00:00:00 |
| 2 | Topic 2 | admin | 2007-08-09 00:00:00 |
+---------+---------+----------+---------------------+
2 rows in set (0.00 sec)
[/tt]

Not quite what I was looking for. Any help please? Thanks!

 

[r937]

SELECT FT.TopicID
     , FT.Subject
     , U.Username
     , FP.DatePosted
  FROM tblForumTopics AS FT 
INNER
  JOIN tblForumPosts AS FP 
    ON FP.TopicID = FT.TopicID
   [red]AND FP.DatePosted =
       ( SELECT MAX(DatePosted)
           FROM tblForumPosts 
          WHERE TopicID = FT.TopicID )[/red]
INNER
  JOIN tblUsers AS U 
    ON U.UserID = FP.UserID
 WHERE FT.ForumID = 8
ORDER 
    BY FP.DatePosted DESC;

r937.com | rudy.ca

 

[bitwise]

Thank you r937! I knew deep down I needed to do a subquery, but since I'm a bit out of practice using SQL I just couldn't quite get my mind to work it out. That's it though. Thanks again!