Select last messages from a table grouped by sender and recipient as well as ordered by date decenda-CodePudding

I have a "messages" MySQL table:

message_time         from_id  to_id     message
2021-11-30 22:50:59    1       3      just a string
2021-11-30 22:51:05    2       3      string 2
2021-11-30 22:51:49    1       3      any string
2021-11-30 22:55:32    1       3      string 4
2021-12-01 23:16:37    2       3      string 5
2021-12-01 23:18:11    2       3      string 6
2021-12-01 23:20:23    1       3      xxxx
2021-12-01 23:22:33    1       3      yyyy
2021-12-01 23:26:45    2       3      zzzz
2021-12-01 23:28:12    3       2      string 7
2021-12-01 23:30:27    1       4      aaaa
2021-12-01 23:40:30    1       4      bbbb
2021-12-01 23:48:32    2       4      cccc
2021-12-01 23:55:34    2       4      message 8
2021-12-01 23:59:36    4       1      1111

I need to get the latest "message" between all id pair combinations of from_id and to_id.
from_id and to_id are exchangeable meaning id pair 1-4 is the same as 4-1, I just need the last message between id pairs.
All these combinations should be ordered by message_time descendant.

Being said that, I would expect to get this result:

message_time         from_id  to_id     message
2021-12-01 23:59:36     4      1       1111
2021-12-01 23:55:34     2      4       message 8
2021-12-01 23:28:12     3      2       string 7
2021-12-01 23:22:33     1      3       yyyy

I am using this query:

SELECT * FROM messages GROUP BY from_id ORDER BY message_time DESC

But it is not working, I run out of ideas.

CodePudding user response：

Can be achieved by this query.

SELECT 
messages.*, 
CONCAT(IF(from_id > to_id, from_id, to_id), IF(from_id > to_id, to_id, from_id)) as conv_id
FROM messages
INNER JOIN (
    SELECT 
    max(message_time) as message_time,
    CONCAT(IF(from_id > to_id, from_id, to_id), IF(from_id > to_id, to_id, from_id)) as conv_id
    FROM messages
    GROUP BY conv_id
) x ON x.message_time = messages.message_time AND x.conv_id = conv_id
ORDER BY messages.message_time DESC;

The key part is to create a temporary column "conv_id" (conversation id) that groups messages between same participants.