SQL Group By and Join

Answer 1

The solution: more subqueries!!

  select * from (
    select t.*, (
      select top 1 ID 
      from Message 
      where ParentThreadID = t.ID 
      order by DateCreated desc 
    ) as MessageID
    from MessageThread t
  ) tm
  left join Message m on tm.MessageID = m.ID 

This should get you all the columns with two nested queries.

Answer 2

How would this work for you:

SELECT (whateverYouWant)
FROM Message m, MessageThread mt
WHERE m.ParentThreadID = mt.ID
AND mt.DateCreated = (SELECT MAX(mt2.DateCreated) 
                      FROM MessageThread mt2 
                      WHERE mt2.ID= mt.ID)

This has the effect of selecting only one row, the one which has the maximum date for that thread. Also, it means that you can select whichever columns you want without having to subquery them which reduces the number of table scans or index scans your query has to do.

Answer 3

First, you can write this without a derived table (as in your OP) by using a subquery like so:

Select ...
From MessageThread As T
Where Id = (
            Select TOP 1 M.Id
            From Message As M
            Where M.ParentThreadId = T.Id
            Order By DateCreated Desc
            )

The equivalent LINQ would be something like:

    var query = from messageThread in MessageThread
                join message in Message on message.ParentThreadId == messageThread.Id
                let first = ( messages.Where( m => m.ParentThreadId == messageThread.Id ).OrderByDescendng(m => m.DateCreated).First().Id )
                where messageThread.Id == first
                select new {MessageThread = messageThread, Message = Message};

EDIT You mentioned that you need the data from Message as well. In which case, simply join to Message.