Почему запрос выполняется неверно?

Question

Есть вот такой пример: sqlfiddle.com/#!9/595fdf/3 Я ожидаю что вернутся записи с id 2 и 3, а возвращаются 1 и 3. Что не так и как исправить?

create table chat_messages
(
    id int auto_increment primary key,
    room_id int not null,
    message varchar(255) not null,
    created_at timestamp null
);

insert into chat_messages (room_id, message, created_at) values
(1, 'message 1', NOW()),
(1, 'message 2', NOW() + interval 1 hour),
(2, 'message 3', NOW() + interval 2 hour);

select *
from chat_messages
where room_id in (1, 2)
group by room_id
order by created_at desc;

Answer 1

SQL:

Select * from chat_messages c
join (select max(created_at) as max_created, room_id
From chat_messages
Group by room_id
) m 
on c.group_id = m.group_id and
c.max_created = m.max_created

Как-то так Group by оператор берет первое попавшееся значеие (то, что успел прочитать) поэтому приходится сначала группировать одну часть в подзапросе, а потом джоинить окончательный результат.

Answer 2

Лучше эту задачу разбить на две части. Сначала напишем запрос, который найдет максимальный id для каждого room_id:

SELECT 
   room_id, 
   MAX(id) AS MaxId
 FROM chat_messages
 GROUP BY room_id

После этого с помощью INNER JOIN этот запрос объединим с выборкой из таблицы chat_messages. Таким образом, будут выбраны строки целиком из таблицы chat_messages, но только те, которые были найдены в первом запросе:

SELECT *
FROM chat_messages AS cm
INNER JOIN
    (SELECT 
       room_id, 
       MAX(id) AS MaxId
     FROM chat_messages
     GROUP BY room_id) group_diff 
ON cm.room_id = group_diff.room_id 
AND cm.id = group_diff.MaxId;

Ссылка: Получить id пользователя, сумму и дату первой выплаты

БЛОГ НА HUSL

Почему запрос выполняется неверно?

Ответы (2 шт):