Как выбрать две последнии записи для каждой группы?

Question

[dhscor]

Помогите, пожалуйста, составить запрос для выбора двух последних сообщений для каждого диалога? Таблица:

+----+--------+-----+-----+-------+-----------+
| id |group-id|user1|user2|message|last-sender|
+----+--------+-----+-----+-------+-----------+

В каждом диалоге, пользователь может отправить лишь одно сообщение, т.е диалог выглядит примерным образом:

user1: message from user1.
user2: message from user2.
user1: message from user1.
user2: message from user2.
e.t.c

Соответственно, желаемый результат, чтобы последнее сообщение было всегда от пользователя != $_SESSION['ID'], а предпоследнее от пользователя = $_SESSION['ID'].

Подобным образом я хочу вывести привью диалогов, не больше 20 штук на странице:

+-------------------------------------------------------+
|                         ДИАЛОГ 1                      |
+--------+-------------+--------------------------------+
|  user1 | hello world | last-sender = $_SESSION['ID']  |
+--------+-------------+--------------------------------+
|  user2 |   hi! :)    | last-sender != $_SESSION['ID'] |
+--------+-------------+--------------------------------+

+-------------------------------------------------------+
|                         ДИАЛОГ 2                      |
+--------+-------------+--------------------------------+
|  user2 | what's up?  | last-sender = $_SESSION['ID']  |
+--------+-------------+--------------------------------+
|  user1 |   sup       | last-sender != $_SESSION['ID'] |
+--------+-------------+--------------------------------+

Я перепробовал уже тысячи способов, но прийти к нужному результату у меня так и не получилось. Может ли кто-нибудь, пожалуйста, помочь? А то я застрял на этом участке кода уже около недели. :(

Comments

[Slava Rozhnev]

Какая версия MySQL?

Answer 1

[Rsa97]

Для MySQL 8:

SELECT *
  FROM (
    SELECT `id`, `group-id`, `user1`, `user2`, `message`,
           ROW_NUMBER() OVER (PARTITION BY `group-id` ORDER BY `id` DESC) AS `row_number`
    FROM `messages`
  ) AS `t`
  WHERE `row_number` < 3
  ORDER BY `group-id`, `id`

Comments

[dhscor]

Rsa97, спасибо большое, все работает! Это то, что и было нужно!

Answer 2

[Slava Rozhnev]

Вот такая сложная конструкция для MySQL 5.x:

select * 
from messages 
join (
  select 
        -- создаем JSON ARRAY из id и берем 2 последних элемента
	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))
;

SQL песочница

Comments

[dhscor]

Slava Rozhnev, спасибо за столь большой запрос! Вот даже очень большое спасибо)
Попробую поэксперементировать и с ним)

[Лентюй]

слишком сложно)) до оконных функций через переменные такое делали

[Лентюй]

да и json добавили в 5.7 вроде, в 5.5. их не было еще... если мне не изменяет память

[Slava Rozhnev]

Лентюй, да, согласен, сложновато получилось :)

Answer 3

[FanatPHP]

Мне кажется, можно и без оконных функций
каким-нибудь select max(id) group by group-id получить айдишники последних 20 разговоров, соответственно этим подзапросом выбрать все последние реплики, ну и еще один подзапрос чтобы выбрать реплику второго адресата

Comments

[Rsa97]

Можно. Но извратно. Но работать должно.

SELECT `id`, `group-id`, `user1`, `user2`, `message`
  WHERE `id` IN (
    (SELECT MAX(`id`)
      FROM `messages`
      GROUP BY `group-id`)
    UNION (SELECT MAX(`id`)
      FROM `messages`
      WHERE `id` NOT IN (
        SELECT MAX(`id`)
          FROM `messages`
          GROUP BY `group-id`
      )
      GROUP BY `group-id`)
  ) AS `t`
  ORDER BY `group-id`, `id`

[FanatPHP]

ну я имел в виду подзапрос прямо в SELECT, но так тоже должно
у него правда там еще условие, что обязательно два участника должно быть, то есть на самом деле не "последние две записи", а чуть сложнее
Ну да авось осилит

[dhscor]

Варианта предложенного Rsa97, мне вполне хватит)

[FanatPHP]

ну где ж хватит-то?
глаза разуй.
ну или определись сначала сам, что тебе нужно