Unable to OrderBy a column-CodePudding

I am really stuck on this:

SELECT DISTINCT
  chats.chat_title,
  chats.chat_object,
  MAX(chats.created_at) AS latest,
  SUM(IF(chats.admin_read = 0, 1, 0)) AS unread,
  chats.id,
  chats.group_id,
  chats.message
FROM chats
WHERE chats.group_id = 82
GROUP BY chats.chat_object
ORDER BY latest

At the moment the groupBy works well but it always shows the oldest chats.message, instead I would like the latest chats.message to show in each chats.chat_object GroupBy . I have been for hours on this, I cannot find a way to do it. I was really expecting MAX(chats.created_at) AS latest to show the latest chats.message

Any idea please?

Thank you!

CodePudding user response：

You can't aggregate partially: the GROUP BY clause requires all columns that are not aggregated in the SELECT clause.

One option is to first aggregate on chat_object and then join the aggregated values with the parent table:

WITH cte AS (
    SELECT chat_object,
           MAX(chats.created_at) AS latest,
           SUM(IF(chats.admin_read = 0, 1, 0)) AS unread
    FROM chats 
    GROUP BY chat_object
)
SELECT chats.chat_title,
       chats.chat_object,
       cte.latest,
       cte.unread,
       chats.id,
       chats.group_id,
       chats.message
FROM chats 
INNER JOIN cte 
        ON chats.chat_object = cte.chat_object
WHERE chats.group_id = 82
ORDER BY latest

You could also use window functions, but it's difficult to generate a solution without input samples.

CodePudding user response：

I spent all night on this and finally found something that works:

SELECT
  C1.chat_title,
  C1.chat_object,
  C1.created_at,
  C1.id,
  MAX(C1.message) AS messages,
  C2.unread,
  C1.group_id
FROM chats C1
  INNER JOIN (SELECT
      C2.created_at,
      C2.chat_object,
      SUM(IF(C2.admin_read = 0, 1, 0)) AS unread
    FROM chats C2
    GROUP BY C2.chat_object) C2
    ON C1.chat_object = C2.chat_object
WHERE C1.group_id = 82
GROUP BY C1.chat_object,
         C1.group_id
ORDER BY C1.id DESC