Как сгруппировать по убыванию?

Question

[viktorross]

Как запросе сгруппировать записи по убыванию? Чтобы вот этот подзапрос возвращал самую последнюю запись, весь запрос брал не первые, а самые последние записи

(select `class_messages`.`chat_message` order by `class_messages`.`id` desc limit 1) as last_message

Но группировка вот здесь судя по всему происходит таким образом, что остаются только первые результаты, т.е по возрастанию

group by `class_messages_chat`.chat_id desc order by class_messages.id desc;

SELECT `class_messages_chat`.`to_user_id`, 
       `class_messages_chat`.`from_user_id`, 
       `class_users`.`id`, 
       `class_users`.`user_status`, 
       `class_users`.`online`, 
       `class_messages_chat`.`chat_id`, 
       `class_messages_chat`.`ad_id`, 
       `class_ads`.user_id, 
       `class_ads`.title, 
       ( select `class_messages`.`chat_message` order by `class_messages`.`id` desc limit 1) as last_message 
FROM `class_messages_chat` 
inner join `class_ads` on `class_messages_chat`.ad_id = `class_ads`.id 
INNER JOIN `class_users` ON `class_users`.`id`=`class_ads`.`user_id` 
inner join `class_messages` on `class_messages_chat`.`chat_id` = `class_messages`.`chat_id` 
WHERE (`class_messages_chat`.`to_user_id` = 133337 or `class_messages_chat`.`from_user_id` = 133337) 
  and class_messages_chat.chat_id is not null 
group by `class_messages_chat`.chat_id desc 
order by class_messages.id desc;

Comments

[Adamos]

Вообще-то мускуль уже на "GROUP BY DESC" должен был сказать "или отсюда, мальчик, и найди в словаре, что такое группировка и сортировка".

[Akina]

В студию:

- Вывод запроса SELECT @@version;
- CREATE TABLE таблицы
- INSERT INTO с примером данных (10 rows max.)
- Требуемый результат для показанных данных

[Akina]

как в моем запросе сгруппировать записи по убыванию?

Нет такого понятия. В принципе. По убыванию бывает сортировка.

[viktorross]

Adamos, это я пробовал так сделать, потом нашёл, что так не вариант

[Adamos]

viktorross, посмотреть в словаре - вариант, проверено.
А вот пытаться решать задачу, условия которой сам не понимаешь - действительно, не вариант.

[ThunderCat]

MIN()/MAX()?

Answer 1

[alexalexes]

Если у вас MySQL 8+ версия, то список чатов получается тривиально - используя оконную функцию:

SELECT A.*
from (
SELECT class_messages.id as class_messages_id,
      `class_messages_chat`.`to_user_id`,
       `class_messages_chat`.`from_user_id`,
       `class_users`.`id`,
       `class_users`.`user_status`,
       `class_users`.`online`,
       `class_messages_chat`.`chat_id`,
       `class_messages_chat`.`ad_id`,
       `class_ads`.user_id,
       `class_ads`.title,
       row_number() over (partition by `class_messages_chat`.`chat_id` order by class_messages.id desc) rn -- этой функцией нумеруем в пределах чата сообщения
    FROM `class_messages_chat`
    inner join `class_ads` on `class_messages_chat`.ad_id = `class_ads`.id
    INNER JOIN `class_users` ON `class_users`.`id`=`class_ads`.`user_id`
    inner join `class_messages` on `class_messages_chat`.`chat_id` = `class_messages`.`chat_id`
WHERE (   `class_messages_chat`.`to_user_id` = 133337
       or `class_messages_chat`.`from_user_id` = 133337)
and class_messages_chat.chat_id is not null
) A
where A.Rn = 1 -- оставляем, все что с первым номером счетчика
order by A.class_messages_id desc

В mySQL 5.7

SELECT A.*
from (
SELECT
      `class_messages_chat`.`to_user_id`,
       `class_messages_chat`.`from_user_id`,
       `class_users`.`id`,
       `class_users`.`user_status`,
       `class_users`.`online`,
       `class_messages_chat`.`chat_id`,
       `class_messages_chat`.`ad_id`,
       `class_ads`.user_id,
       `class_ads`.title,
  IF(@prev <>  `class_messages_chat`.`chat_id`, @rn:=1,@rn), @prev:= `class_messages_chat`.`chat_id`, @rn:=@rn+1 AS rn -- этой функцией нумеруем в пределах чата сообщения
    FROM `class_messages_chat`
    inner join `class_ads` on `class_messages_chat`.ad_id = `class_ads`.id
    INNER JOIN `class_users` ON `class_users`.`id`=`class_ads`.`user_id`
    inner join `class_messages` on `class_messages_chat`.`chat_id` = `class_messages`.`chat_id`,
     (SELECT @rn:=1)rn,
     (SELECT @prev:=-1)prev
WHERE (   `class_messages_chat`.`to_user_id` = 133337
       or `class_messages_chat`.`from_user_id` = 133337)
and class_messages_chat.chat_id is not null
order by class_messages.id desc
) A
where A.Rn = 1 -- оставляем, все что с первым номером счетчика

Второй вариант для 5.7, если некорректно будет работать сортировка.

select B.*
from (SELECT A.*,
           IF(@prev <> A.chat_id, @rn:=1,@rn), @prev:= A.chat_id, @rn:=@rn+1 AS rn -- этой функцией нумеруем в пределах чата сообщения
from (
SELECT
      `class_messages_chat`.`to_user_id`,
       `class_messages_chat`.`from_user_id`,
       `class_users`.`id`,
       `class_users`.`user_status`,
       `class_users`.`online`,
       `class_messages_chat`.`chat_id`,
       `class_messages_chat`.`ad_id`,
       `class_ads`.user_id,
       `class_ads`.title
    FROM `class_messages_chat`
    inner join `class_ads` on `class_messages_chat`.ad_id = `class_ads`.id
    INNER JOIN `class_users` ON `class_users`.`id`=`class_ads`.`user_id`
    inner join `class_messages` on `class_messages_chat`.`chat_id` = `class_messages`.`chat_id`
WHERE (   `class_messages_chat`.`to_user_id` = 133337
       or `class_messages_chat`.`from_user_id` = 133337)
and class_messages_chat.chat_id is not null
order by class_messages.id desc
) A,
(SELECT @rn:=1)rn,
     (SELECT @prev:=-1)prev
) B
where B.Rn = 1 -- оставляем, все что с первым номером счетчика

Comments

[viktorross]

alexalexes, спасибо большое, но у меня версия 5.7, забыл уточнить сразу, как в этом случае быть?

[alexalexes]

Дополнил ответ. Проверить не могу, но если order by class_messages.id desc сработает корректно для введенных переменных в if, то сработать должно корректно.
Если нет, то во внутреннем запросе нужно провести сортировку order by class_messages.id desc.
В промежуточном запросе сделать финт с ушами if, добавить инициализацию переменных через (SELECT @rn:=1)rn, (SELECT @prev:=-1)prev.
А в самом внешнем запросе сделать отсечение A.Rn = 1.

[viktorross]

alexalexes, пишет, что нарушен синтаксис около

(
SELECT
      `class_messages_chat`.`to_user_id`,

, в таком запросе я не смогу исправить ошибку..

[alexalexes]

Наверное, у вас написано было, что синтаксическая ошибка возле from - там запятая лишняя была.

[viktorross]

alexalexes, все равно ошибка там же..

#1064 - У вас ошибка в запросе. Изучите документацию по используемой версии MySQL на предмет корректного синтаксиса около '(
SELECT
      `class_messages_chat`.`to_user_id`,
       `class_messages_cha' на строке 4

[alexalexes]

from-ов не хватало перед подзапросами.

[viktorross]

alexalexes, спасибо огромное, теперь второй работает, но возвращает 0 результатов, а первый вариант также ошибку выдает, но уже на другом месте

#1064 - У вас ошибка в запросе. Изучите документацию по используемой версии MySQL на предмет корректного синтаксиса около '(partition by `class_messages_chat`.`chat_id` order by class_messages.id desc) r' на строке 13

[alexalexes]

У вас же версия 5.7, оконные функции не будут работать, даже можно не пытаться запустить то, что содержит over partition by.

но возвращает 0 результатов

закомментируйте строку:

where A.Rn = 1

и добавьте вывод переменных до if и после:

@prev as before_prev, 
@rn as before_rn,
IF(@prev <> A.chat_id, @rn:=1,@rn), @prev:= A.chat_id, @rn:=@rn+1 AS rn,
@prev as after_prev, 
@rn as after_rn

И отлаживайте.
Нужно добиться поведения after_rn:
1) чтобы rn возрастало по ходу сортировки order by class_messages.id desc.
2) чтобы rn становилось 1 когда первая строчка или когда chat_id меняется на другое значение.

[viktorross]

alexalexes, спасибо, вот это вроде бы работает как надо, записи идут начиная с большего class_messages.id , но теперь нет группировки, т.е выводятся все существующие записи в нужном порядке, а сгруппировать их нужно по class_messages.chat_id

select B.*
from (SELECT A.*,
         @prev as before_prev, 
@rn as before_rn,
IF(@prev <> A.chat_id, @rn:=1,@rn), @prev:= A.chat_id, @rn:=@rn+1 AS rn,
@prev as after_prev, 
@rn as after_rn
from (
SELECT
    `class_messages`.`id` as mess_id,
    `class_messages`.`chat_message`,
      `class_messages_chat`.`to_user_id`,
       `class_messages_chat`.`from_user_id`,
       `class_users`.`id`,
       `class_users`.`user_status`,
       `class_users`.`online`,
       `class_messages_chat`.`chat_id`,
       `class_messages_chat`.`ad_id`,
       `class_ads`.user_id,
       `class_ads`.title
    FROM `class_messages_chat`
    inner join `class_ads` on `class_messages_chat`.ad_id = `class_ads`.id
    INNER JOIN `class_users` ON `class_users`.`id`=`class_ads`.`user_id`
    inner join `class_messages` on `class_messages_chat`.`chat_id` = `class_messages`.`chat_id`
WHERE (   `class_messages`.`to_user_id` = 133337
       or `class_messages`.`from_user_id` = 133337)
and class_messages_chat.chat_id 
order by class_messages.id desc
) A,
(SELECT @rn:=1)rn,
     (SELECT @prev:=-1)prev
) B

[alexalexes]

А как ведет себя переменная rn?

[viktorross]

alexalexes,
1.
2.
3.

[alexalexes]

В самой внутренней выборке нужно отсортировать так:

order by `class_messages_chat`.`chat_id`, class_messages.id desc

А в самой внешней - так (возможно, она не понадобится):
order by B.mess_id desc
Возможно, время выполнения увеличится, так как будут сортироваться сообщения в самых древних чатах, но по логике, нумерация должна правильно работать.
Посмотрите, как после таких исправлений сортировки rn будет меняться.
Если все нормально, то можно вернуть получение первого номера rn:
where A.Rn = 1

[viktorross]

alexalexes, та же самая история вроде бы, он просто выкатывает все записи, без группировки, как это делает group by, по сути-то нужно чтобы он взял все class_messages_chat.chat_id, где соответствуют поля
`class_messages_chat`.`to_user_id` =133337 или `class_messages_chat`.`from_user_id` = 133337, нашел эти chat_id в таблице class_messages и выдал последние записи, понятно, что там еще несколько таблиц участвует, но по ним группировок нет, сама суть именно в последних chat_id, но это видимо крайне сложно решить, хотя выглядит просто

[alexalexes]

У вас в этом запросе группировки не будет в принципе, нужно выдернуть некие "первые записи" - это и будет что-то похожее на группировку.
Нужно от rn добиться такого же поведения, если бы у вас возможность работать с partition by

row_number() over (partition by `class_messages_chat`.`chat_id` order by class_messages.id desc) rn

С оконкой сложно накосячить в этой строчке, а вот с if и с переменными - как два пальца. Поэтому нужно внимательно их отлаживать.