SQL - need a query to get most recent message in e

Question

In my app, a "message thread" is defined as all messages between two or more users, and is not nested. It needs to work like Facebook messages.

I need a query that generates a list of all "message threads" in which a user is a member, sorted by threads with the most recent activity, descending. The result is a table of distinct threads, where each row contains the threadID, postDate and messageBody.

Here's my schema:

MessageThreads (threadID, lastPostDate)
MessageThreadUsers (threadFK, userFK)
Messages (messageID, threadFK, senderFK, postDate, messageBody)
Users (userID, userName, userEmail, ...)

To start with, this query gives me all messages from all threads that the user is in:

SELECT * FROM MessageThreadUsers
JOIN Messages ON MessageThreadUsers.threadFK = Messages.threadFK
WHERE MessageThreadUsers.userFK = 'usr_developer'
ORDER BY messageDate DESC

But how would I get only the most recent? I think I would use the MAX(messageDate) function, but how does that work in a JOIN like this? And how would I pull a single row with message data for each thread?

It would help quite a bit if you can post your answer in TSQL, but any help is appreciated. Thank you!

Answer 1

This turned out to be not as difficult as I first thought. Because the most recent post date is being stored in the thread, I don't have to aggregate on the messageDate in the Messages table. Here's my query:

SELECT DISTINCT 
    MessageThreadUsers.threadFK, 
    MessageThreads.threadDate, 
    [Messages].MessageBody, 
    [Messages].senderFK,
    Users.userFullName
FROM MessageThreadUsers
JOIN MessageThreads ON MessageThreadUsers.threadFK = MessageThreads.threadID
JOIN Messages ON MessageThreads.threadDate = Messages.messageDate
JOIN Users ON Messages.senderFK = Users.userID
WHERE userFK = 'usr_developer'

Answer 2

If you have the right order, you should get the "top hit" by adding:

LIMIT 1