how can I get the last message from my`msg`field with`GROUP BY`

Asked

Viewed 211 times

2

good evening, I wonder how can I bring the last message from my group by, because I’m not getting it. is always bringing the first occurrence of the consultation and I always need to take the last message from that group. how can I do that someone could give a help?

this is my consultation

SELECT key_section, visualizado, msg, MAX(id_msg)
FROM chat 
GROUP BY key_section 
ORDER BY id_msg desc;
mysql sql group-by
  • 4

    this select will not work, all fields of select must have stapling functions or be in the group by: group by key_section, visualizado, msg

  • here is a brief explanation of this: https://answall.com/a/320494/57220

  • It would be interesting to identify what determines the last message, I saw that you have key_section and id_msg, which one should be the key? id_msg could simply be the identifier and key_section the sequential, so the key_section would be more indicated, now I imagine it might have a DATA_HORA field in this table so the last message could be identified there, agreeing with @Ricardo Punctual, all fields outside of MAX must be in group by, although Mysql should not claim the SQL standard.

2 answers

3

The Problem

It turns out that the columns of visualizado and msg are not being used in GROUP_BY and are not being used by any aggregation function (SUM(), AVG(), etc..).

In MYSQL there is a flag called only_full_group_by, if it is enabled, executing this query will return an error stating precisely that the selected columns are not in group by or in some aggregation function.

If it is disabled the query will be executed, but it is not guaranteed which value will be returned to columns that do not meet the condition of group_by. For example, suppose the following records:

+-------------+------------------+------------+--------+
| key_section | visualizado      | msg        | id_msg |
+-------------+------------------+------------+--------+
| 1           | 2019-09-03 08:32 | Mensagem 1 | 1      |
+-------------+------------------+------------+--------+
| 1           | 2019-09-05 17:00 | Mensagem 2 | 2      |
+-------------+------------------+------------+--------+

Grouping by key_section (value 1), what result would be returned in the other columns? The return order of the results is not guaranteed, which can cause you inconvenience. The exception to this case occurs when you are guaranteed to have the same value in the other columns, then the results would be "grouped"

Maybe you could think about disabling the flag only_full_group_by but this would only be hiding the problem. No chance...

Solution

When I find a problem like this, I use a solution known as greatest n per group where we select the "groups" and then select the values of these groups.

In your specific case, I would write the query this way:

SELECT 
    max_chat.key_section,
    max_chat.id_msg,
    chat.visualizado,
    chat.msg
FROM
    chat
        INNER JOIN
    (SELECT 
        key_section, MAX(id_msg) AS id_msg
    FROM
        chat
    GROUP BY key_section) AS max_chat ON max_chat.key_section = chat.key_section
        AND chat.id_msg = max_chat.id_msg
ORDER BY max_chat.id_msg DESC;

So we would select all the views and messages of each key_section and their largest id_msg.

1

The most practical way I believe it to be using LIMIT:

SELECT key_section, visualizado, msg, id_msg
FROM chat 
ORDER BY id_msg desc
LIMIT 1;

Thus, only one line will be returned, ordered downwards by id_msg.

  • Case the flag only_full_group_by is enabled, this query will return error. The viewing columns, msg and id_msg are not described in GROUP BY.

  • 1

    you’re right, @Samueltorga. Anyway, the group by not necessary. I changed the answer!

Browser other questions tagged mysql sql group-by

You are not signed in. Login or sign up in order to post.