SQL select latest message of every conversation received AND sent

Question

This question SQL select only rows with max value on a column doesn't solve my problem although it has been marked as duplicate.

It assumes my columns from_id and to_id are primary keys, when they don't have such constraint (see code provided bellow). If they were primary keys, I couldn't store my messages in the same table. As a result the SQL query of this answer prints all duplicates multiple times, which is not what I want. Please see expected behaviour bellow.

Expected behaviour : I need to select the latest message from all conversations, regardless of whether the user is only sender, recipient, or both. Each conversation/thread should only be displayed once.

Example : when querying this table, my SQL statement should only output msg3 and msg4, ignoring all the previous messages John and Alice exchanged.

Here is the closest query I could write. Problem is this query only selects conversations where user received a message. I'm stuck adding conversations where user is only sender (he didn't get any reply) to the selection.

SELECT * FROM messages where `to_id` = '1' GROUP BY `from_id` ORDER BY `send_date` ASC

Here are users and messages tables:

CREATE TABLE users (
    id INT(11) AUTO_INCREMENT PRIMARY KEY, 
    name VARCHAR(128) NOT NULL
);

CREATE TABLE messages (
    id INT(11) AUTO_INCREMENT PRIMARY KEY, 
    to_id INT(11) NOT NULL,   //recipient id to match to current user id
    from_id INT(11) NOT NULL, //sender id to match to current user id
    send_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    content TEXT
);

Question: How can I do this using a single SQL query ? Or should I change my data structure using three tables instead of one ?

Gordon Linoff · Accepted Answer · 2017-10-01T16:24:23.577

I would first get the ids. You can do this using least() and greatest():

select least(m.to_id, m.from_id) as id1,
       greatest(m.to_id, m.from_id) as id2, max(m.id) as max_id
from messages m
group by id1, id2;

You can then get the complete information about the message by joining back:

select m.*
from messages m
where m.id in (select max(m.id) as max_id
               from messages m
               group by least(m.to_id, m.from_id), greatest(m.to_id, m.from_id)
              );

Note: In older versions of MySQL, putting the subquery in the from clause and using join is much more efficient.

SQL select latest message of every conversation received AND sent

1 Answers1