Private Chat System Mysql Query To Display Last Message Of Sender/receiver

Here I am extending my previous question. Private chat system MYSQL query ORDERBY and GROUPBY In addition, I created one more table called users from where I will get users info. m

Solution 1:

What you're asking for is basically to group by thread rather than sender. Threads can be initiated between two people, regardless of being sender / receiver.

To accomplish this, I would recommend changing your data structure similar to what's been discussed here: Is there a simpler way to achieve this style of user messaging?

Solution 2:

If this is a group chat don't limit to a receiver_id like messages.receiver_id = 102

SELECT users.name,
       temp.MESSAGE_TEXT,
       temp.created_time
FROM
  (SELECT sender_id, MESSAGE_TEXT, created_time
   FROM messages
   WHERE created_time IN
       (SELECT MAX(created_time)
        FROM messages
        GROUPBY sender_id)) AS temp
LEFT JOIN users ON temp.sender_id = users.id;

show last message each sender sent