SQL - need a query to get most recent message in each thread that a user is part of

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!

score 0 · Answer 1 · answered Mar 29 '12 at 06:15

0

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

LIMIT 1

answered Mar 29 '12 at 06:15

XTL

851
1
8
23

1

I'm thinking that limiting the result set would be done for the final set of threads as I page the results where MAX () would give me the latest post. But this answer doesn't get to the heart of the problem, which is: how would I join only the most recent message from a thread into a list of threads? – Redtopia Mar 29 '12 at 13:41

score 0 · Answer 2 · answered Mar 29 '12 at 14:10

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'

SQL - need a query to get most recent message in each thread that a user is part of

2 Answers2

Linked