CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
Hal_9100
Hal_9100

Reputation: 951

SQL select latest message of every conversation received AND sent

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.

enter image description here

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 ?

Upvotes: 1

Views: 1881

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

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.

Upvotes: 6

Related Questions