Select all most recent records

Question

I have this two tables (and their main fields):

    Target:                        Message:

    Id     IdMessage               Id        IdMain        IdReply        DatePosted
    ================               =================================================
    1          7                   7          null          null          2014-12-11 13:45:38.633
    2          8                   8           7             7            2014-12-20 13:45:38.633
    3          9                   9           7             8            2015-01-05 13:45:38.633
    4          11                  11          null         null          2014-12-17 13:45:38.633
    5          13                  13          null         null          2014-12-17 13:45:38.633
    6          14                  14          13            13           2015-02-11 13:45:38.633

And I need to select the most recent messages/replies for each main message:

    Target:                        Message:

    Id     IdMessage               Id        IdMain        IdReply        DatePosted
    ================               =================================================
    3          9                   9           7             8            2015-01-05 13:45:38.633
    4          11                  11          null         null          2014-12-17 13:45:38.633
    6          14                  14          13            13           2015-02-11 13:45:38.633

This's my code:

select targt.IdMessage as Id,
       msg.Subject as Title,
       msg.IdMain as IdReply,
       msg.DatePosted as DatePosted,
       targt.IdTarget as IdUserTarget,
       targt.DateReceived as DateReceived,
       targt.IdUserCreate as IdUserCreate,
       usr.FirstName as UserCreateFirstName,
       usr.LastName as UserCreateLastName
from Target targt
inner join Message msg on targt.IdMessage = msg.Id
inner join [User] usr on msg.IdUserCreate = usr.Id
where targt.IsActive = 1 and msg.IsActive = 1 and
msg.DatePosted = (select max(msgComp.DatePosted) from Message msgComp
                 where msgComp.IsActive = 1 and ((msgComp.Id = msg.IdMain and msgComp.IdMain is null) 
                        or msgComp.IdMain = msg.IdMain))

but this code only return the most recent record at all... How can I solve this problem and recent records for each main message?

Answer 1

Your query includes a lot of tables and columns that aren't listed in your sample data. From this query you can join and pull in other things:

;WITH lastMessage AS
(
    SELECT Id, IdMain, IdReply, DatePosted, rn = ROW_NUMBER() OVER 
      (PARTITION BY COALESCE(IdMain, Id) ORDER BY DatePosted DESC, Id)
    FROM dbo.Message
)
SELECT t.Id, t.IdMessage, m.IdMain, m.IdReply, m.DatePosted
  FROM lastMessage AS m
  INNER JOIN dbo.Target AS t
  ON m.Id = t.IdMessage
  WHERE m.rn = 1;

SQLfiddle demo