How to display the latest message?

Question

MySQL

Good day. I'm trying to get the last message from the dialogue with each user, as a result I get not the last one, but the first ones with GROUP BY, Please tell me. I think that it is necessary to output the sender and receiver based on the time, the message.created_at is repeated

#CREATE DATABASE test;
USE test;

CREATE TABLE users (
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255)
);

INSERT INTO users (username) 
  VALUES 
    ('User 1'),
    ('User 2'),
    ('User 3'),
    ('User 4');
    
CREATE TABLE message (
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    sender INTEGER NOT NULL,
    receiver INTEGER NOT NULL,
    text_message TEXT,
    created_at TIMESTAMP DEFAULT NOW(),
    FOREIGN KEY (sender)
        REFERENCES users (id)
        ON DELETE CASCADE,
    FOREIGN KEY (receiver)
        REFERENCES users (id)
        ON DELETE CASCADE
);

INSERT INTO message (sender, receiver, text_message) 
  VALUES 
    #(1, 2, 'Hi User 2 part 1');
    #(1, 3, 'Hi User 3 part 1');
    #(1, 2, 'Hi User 2 part 2');
    #(1, 3, 'Hi User 3 part 2');
    (2, 1, 'Hi User 1');
    
SELECT 
    *
FROM
    message;
    
SELECT 
    *
FROM
    message
        INNER JOIN
    users ON message.sender = users.id;
    
SELECT 
    message.id,
    message.sender,
    message.receiver,
    users.username,
    message.text_message,
    message.created_at
FROM
    message
        INNER JOIN
    users ON message.sender = users.id
WHERE
    message.sender = 1
        AND message.receiver = 2
        OR message.sender = 2
        AND message.receiver = 1
ORDER BY message.created_at;
 
# Не верно(
SELECT 
    message.id,
    message.sender,
    message.receiver,
    users.username,
    message.text_message,
    message.created_at
FROM
    message
        INNER JOIN
    users ON message.sender = users.id
WHERE
    message.sender = 1
        OR message.receiver = 1
GROUP BY message.sender, message.receiver
ORDER BY message.created_at;

Should be:

text_message
Hi User 2 part 2
Hi User 3 part 2
'Hi User 1

Answer 1

If we focus on an example, and not on a task description, then

WITH
cte AS ( SELECT *, 
                ROW_NUMBER() OVER (PARTITION BY sender, receiver ORDER BY created_at DESC) rn
         FROM message )
SELECT u1.username sendername,
       u2.username receivername,
       cte.text_message,
       cte.created_at
FROM cte 
JOIN users u1 ON cte.sender = u1.id
JOIN users u2 ON cte.receiver = u2.id
WHERE cte.rn = 1

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=a7a647...