Q:

Hello need help with on how to do this in SQL Server News from Forum Topics/Messages

Okay I have three tables that all communicate with each other.
ForumTopic t
ForumMessage m
ForumUser u

What I am trying to do is get the first message of each Topic.
I have tried this  
  SELECT
   m.[Message], m.[TopicID], m.[Posted], u.Name, t.[Views], t.NumPosts,
   t.Topic
   FROM [ForumMessage] m
   INNER JOIN ( SELECT TopicID, Topic, [Views], NumPosts, ForumID
    FROM [ForumTopic]
    GROUP BY TopicID, Topic, [Views], NumPosts, ForumID ) t ON t.TopicID = m.TopicID
   INNER JOIN [ForumUser] u
   ON u.UserID = m.UserID
   WHERE t.ForumID IN(1,2)
   ORDER BY m.Posted DESC;

And the Result is listed below
 Message    TopicID Posted  Name    Views   NumPosts    Topic
    6   2009-07-20 18:14:06.270 Ravenal 26  3   GENESIS 2.5.1a RELEASE
    6   2009-07-20 18:08:51.027 Ryan    26  3   GENESIS 2.5.1a RELEASE
    6   2009-07-20 17:06:33.550 Ravenal 26  3   GENESIS 2.5.1a RELEASE
    4   2009-07-17 14:22:47.560 Ravenal 14  1   MyGameTools IRC
    3   2009-07-17 01:09:22.403 Ravenal 43  1   GENESIS 2.5.0b RELEASE
    2   2009-07-17 00:48:30.873 Ravenal 44  2   GENESIS 2.5.0a RELEASE
    2   2009-07-16 23:08:44.830 Ravenal 44  2   GENESIS 2.5.0a RELEASE
    1   2009-07-16 23:03:11.790 Ravenal 20  1   Welcome to MyGameTools

So I am trying to figure out how to make it so that it ends up looking like this
 Message    TopicID Posted  Name    Views   NumPosts    Topic
    6   2009-07-20 18:14:06.270 Ravenal 26  3   GENESIS 2.5.1a RELEASE
    4   2009-07-17 14:22:47.560 Ravenal 14  1   MyGameTools IRC
    3   2009-07-17 01:09:22.403 Ravenal 43  1   GENESIS 2.5.0b RELEASE
    1   2009-07-16 23:03:11.790 Ravenal 20  1   Welcome to MyGameTools

Any help will be much appreciated.

A:

Have you tried using a derived table to get the max posted datetime for each topic from the ForumMessages table?
eg
SELECT   m.[Message], m.[TopicID], m.[Posted], u.Name, t.[Views], t.NumPosts,   t.Topic   FROM 
( SELECT TopicID, Max(Posted) MaxPosted
  FROM ForumMessage
  GROUP BY TopicID ) MaxMessage
INNER JOIN [ForumMessage] m   
  ON m.TopicID = maxMessage.TopicID
  AND m.Posted = maxMessage.MaxPosted
INNER JOIN ( SELECT TopicID, Topic, [Views], NumPosts, ForumID
    FROM [ForumTopic]
    GROUP BY TopicID, Topic, [Views], NumPosts, ForumID ) t 
  ON t.TopicID = m.TopicID
INNER JOIN [ForumUser] u
   ON u.UserID = m.UserID   
WHERE t.ForumID IN(1,2)   
ORDER BY m.Posted DESC;

