Getting the most recent message from each chat thread in MySQL

Other Questions MySQL
1

I’m trying to fetch the latest message from every conversation thread for a specific user ID. The tricky part is when the most recent message was sent TO the user, I need to get the last message FROM the other person instead.

Here’s my sample data structure:

+--------+----------+--------+---------+
| msgID  | senderID | userID | content |
+--------+----------+--------+---------+
| 1      | 1        | 2      | 'hello' |
| 2      | 1        | 3      | 'hey'   |
| 3      | 2        | 1      | 'hi'    |
| 4      | 2        | 1      | 'sup'   |
| 5      | 3        | 2      | 'yo'    |
+--------+----------+--------+---------+

For senderID=1, I should get messages with content ‘hey’ and ‘sup’.

My current query gets all recent messages between users but doesn’t filter correctly:

SELECT
    M.msgID,
    M.senderID, M.userID,
    M.content, M.status, M.timestamp
FROM Messages AS M
INNER JOIN
    (
        SELECT
            MAX(msgID) AS latestMsgID
        FROM Messages
        GROUP BY senderID, userID
    ) LM
    ON M.msgID = LM.latestMsgID
WHERE M.senderID = 1 OR M.userID = 1
ORDER BY M.msgID DESC

This query still includes message ID 1 when it should only show IDs 2 and 4. How can I fix this logic?

2

Your problem is you’re grouping by both senderID and userID, so each direction becomes its own thread. You need to group conversations between two people no matter who sent what. I hit this same issue building a chat system last year. Create a conversation identifier that stays the same regardless of who’s messaging. Here’s what worked:

SELECT M.msgID, M.senderID, M.userID, M.content, M.status, M.timestamp
FROM Messages M
INNER JOIN (
    SELECT MAX(msgID) as latestMsgID
    FROM Messages
    WHERE senderID != 1 AND userID = 1
    GROUP BY senderID
) LM ON M.msgID = LM.latestMsgID
ORDER BY M.msgID DESC;

This grabs the latest message FROM each user TO your target user (ID=1). Filter for senderID != 1 and userID = 1, then group by senderID to get the newest message from each person you’ve chatted with.

3

I hit this exact problem building a messaging interface for our internal tool. You need to normalize the conversation pairs first, then grab the latest message from each participant. Don’t group by individual sender/receiver combos - create a unified conversation view instead. sql WITH conversation_pairs AS ( SELECT CASE WHEN senderID < userID THEN senderID ELSE userID END as user1, CASE WHEN senderID < userID THEN userID ELSE senderID END as user2, MAX(msgID) as latest_msg FROM Messages WHERE senderID = 1 OR userID = 1 GROUP BY user1, user2 ) SELECT m.* FROM Messages m JOIN conversation_pairs cp ON m.msgID = cp.latest_msg WHERE m.senderID != 1; This treats each conversation as bidirectional and gets you the most recent message from the other person in each thread. The CASE statements normalize user pairs so a conversation between users 1 and 3 always looks the same, no matter who sent the message.

4

You’re overcomplicating this. Use a self join - join the messages table with itself to find the latest message per conversation pair. Try SELECT m1.* FROM Messages m1 LEFT JOIN Messages m2 ON (m1.senderID = m2.senderID AND m1.userID = m2.userID AND m1.msgID < m2.msgID) WHERE m2.msgID IS NULL - it’ll work better than grouping.

5

Window functions work great for this if you’re on a newer MySQL version. I ran into the same thing building a customer support system - you need to rank messages in each thread and filter from there.

SELECT msgID, senderID, userID, content, status, timestamp
FROM (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY LEAST(senderID, userID), GREATEST(senderID, userID) 
               ORDER BY msgID DESC
           ) as rn
    FROM Messages
    WHERE senderID = 1 OR userID = 1
) ranked
WHERE rn = 1 AND senderID != 1;

LEAST/GREATEST gives you a consistent conversation ID no matter who sent what. ROW_NUMBER ranks by newest message in each thread. Then just filter out your own messages with senderID != 1. Way cleaner than trying to do complex joins for bidirectional conversations.