SQL question for e-mail thread view

Question

I would like to query back a list of threads, with each of the conversation showing the latest message max(date), and a count of how many messages in the conversation count(*), group by threadID.

Here's the SQL table

Message
-------------
messageID (int)
subject (varchar)
...
date (date)
threadID (guid)

How do I produce the following in one single query? in a thread view like in Gmail?

Or is the data structure wrong? Maybe I should denormalize the required data to a Thread table? Make use of trigger to update the last message ID and count?

Thanks!

update I also need the subject of the 'max([date])', and order the thread by the date of the last message, thank you!

Answer 1

Assuming threadID is a unique link for a conversation, unless I'm missing something, this should work:

SELECT threadID, count(messageID) as MessageCount, max([date]) as MaxDate
FROM Message
GROUP BY threadID

You may also want to change the date column name, since that's a reserved word.

EDIT for requirements change

Since you haven't specified if the e-mail subject may change ("Hey" in the first e-mail may be "RE: Hey" in the second for a response), I've done a subquery so you can pull any information from the Message table (M1) you want.

SELECT M1.subject, M1.date, M2.MessageCount
FROM 
Message M1
INNER JOIN (
    SELECT threadID, count(messageID) as MessageCount, 
         max([date]) as MaxDate, max(messageID) as MaxMessageID
    FROM [Message]
    GROUP BY threadID
) M2 ON
M1.threadID = M2.threadID and M1.MessageID = M2.MaxMessageID
ORDER BY M1.[date] DESC