SQLize Online / PHPize Online  /  SQLtest Online

create table messages ( id int AUTO_INCREMENT PRIMARY KEY, group_id int, message text, INDEX (group_id) ); INSERT INTO messages (id, group_id, message) VALUES (10, 1, 'Message1'), (9, 1, 'Message2'), (8, 2, 'Message1'), (7, 3, 'Message1'), (6, 3, 'Message2'), (5, 3, 'Message3'), (4, 1, 'Message3'), (3, 2, 'Message2'), (2, 4, 'Message1'), (1, 3, 'Message4'); select * from messages join ( select IF ( JSON_LENGTH(json_arrayagg(id))>2, JSON_EXTRACT(json_arrayagg(id), CONCAT("$[",JSON_LENGTH(json_arrayagg(id))-2,"]"), CONCAT("$[",JSON_LENGTH(json_arrayagg(id))-1,"]")), json_arrayagg(id) ) ids, group_id from messages group by group_id) last_messages on json_contains(ids, CAST(messages.id AS JSON)) ;