mesaga

DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS messages;
create table if not EXISTS users(
id INTEGER PRIMARY KEY,
name text ,
password text
);
create table if not EXISTS messages(
id INTEGER PRIMARY KEY,
from_id INTEGER,
to_id INTEGER,
message Text
);

INSERT INTO users(name, password) VALUES
('Vasia','37'),
('Dasha','41'),
('Sasha','41'),
('Pasha','41'),
('Dan', '41'),
('Sam', '28');
SELECT 'Все пользователи';
SELECT * from users;
SELECT '----------------------------';
INSERT INTO messages(from_id, to_id, message) VALUES
('1', '2', 'Dasha hello!'),
('2', '1', 'Vasia norm'),
('4', '5', 'How are you?'),
('5', '6', 'Go'),
('4', '1', 'Fantastic!'),
('6', '1', 'No comment'),
('6', '2', 'Slowly'),
('6', '5', 'Dan, Dan ,Dan'),
('1', '2', 'WTF'),
('1', '2', 'Ok');


SELECT 'Последнее отправленное сообщение каждого пользователя';

SELECT m.id, m.from_id, m.to_id, m.message
FROM messages m
JOIN (
    SELECT from_id, MAX(id) AS max_id
    FROM messages
    GROUP BY from_id
) latest ON m.from_id = latest.from_id AND m.id = latest.max_id;

SELECT '----------------------------';
SELECT 'Последнее полученное сообщение каждого пользователя';

SELECT m.id, m.from_id, m.to_id, m.message
FROM messages m
JOIN (
    SELECT to_id, MAX(id) AS max_id
    FROM messages
    GROUP BY to_id
) latest ON m.to_id = latest.to_id AND m.id = latest.max_id;


SELECT '----------------------------';
SELECT 'Последнее сообщение каждого пользователя';
SELECT u.id as 'id', u.name as 'Пользователь', m.id AS message_id, m.to_id, m.message
FROM users u
LEFT JOIN (
    SELECT m.from_id, m.id, m.to_id, m.message
    FROM messages m
    JOIN (
        SELECT from_id, MAX(id) AS max_id
        FROM messages
        GROUP BY from_id
    ) latest ON m.from_id = latest.from_id AND m.id = latest.max_id
) m ON u.id = m.from_id
ORDER BY u.id;