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.

From stackoverflow Ravenal

• It is giving multiple records for a message because you are JOINing it with user table & message has multiple users (other than Topic 4,3,1).

  EDIT: You will have to reduce the output of outer query by limiting to give 1 record (as per max(PostedDateTime).

  Ravenal : Actually, User has nothing to do with it... They are all posted by one person except one other person. Ravenal = Me, Ryan is someone who posted in Topic #6... The thing I am trying to do is Group BY TopicID of the ForumMessage. And in MySQL I can do this easily but lost when it comes to doing it in MsSQL

  shahkalpesh : Is TopicID a date column (atleast it looks like it)? Is Posted a varchar column?

  Ravenal : No TopicID is a integer unique idenitiy column :) same with MessageID

  shahkalpesh : Glad that the issue is resolved. I guess formatting makes it look little weird.
  From shahkalpesh

• 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;
  

  Ravenal : That worked perfectly dude, thanks... I just need to modify it to show the first post rather the or something like that

  shahkalpesh : Exactly what I tried to say. Thanks Darren.
  From Darren Gosbell

• For reference for those who may do the same exact thing I was doing... This was my solution to my result, after the help from Darren.

  SELECT  m.[Message], m.[TopicID], m.[Posted], u.Name, t.[Views], t.NumPosts,   t.Topic   FROM 
  ( SELECT TopicID, Min(Posted) MaxPosted
    FROM ForumMessage
    GROUP BY TopicID ) MaxMessage
  INNER JOIN [ForumMessage] m   
    ON m.TopicID = maxMessage.TopicID
    AND m.Posted = maxMessage.MaxPosted
  INNER JOIN [ForumTopic] 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
  

  From Ravenal