SQL Group By and Join

I feel totally stupid. I'm rusty with my sql.

I have two tables, Message and MessageThread. Each message belongs to one MessageThread using ParentTHreadID as a Foreign Key. You probably can see where this is going.

Well, I want to do something like this. I want to get columns from both tables, messages and threads, but where the message's datecreated is th开发者_运维问答e maximum in that thread. So each record will contain the thread columns and the columns for one message record that is the most recent posted in that message thread.

Here is what I have so far which gets me all the thread columns and the ID of the message. It works, but uses a subquery and I'd have to make a bunch of other subqueries to get the other columns. Yuck.

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

from MessageThread t

Bonus points to anyone who can not only give me sql, but linq to sql or linq to nhibernate.

Thanks, Craig

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.

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.

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.