select with MAX() not showing expected result

Question

So i have this table.

 id     header_id   message_from           message         message_date            attachment
    1   0920-0001   USR-0920-0001   MESSAGE ID 1    18/09/2020 04:11    
    3   0920-0001   USR-0920-0001                   18/09/2020 11:15    862db13b42d569b4afe69828736f4ad8.jpg
    4   0920-0001   USR-0920-0001   MESSAGE ID 4    18/09/2020 11:16    
    5   0920-0001   ADMIN           MESSAGE ID 5    18/09/2020 11:16    
    6   0920-0001   ADMIN           MESSAGE ID 6    18/09/2020 11:16    
    7   0920-0002   USR-0920-0001     Hi            18/09/2020 11:52

i want to achieve this result

    id  header_id   message_from    message         message_date      attachment
     6  0920-0001   ADMIN           MESSAGE ID 6    18/09/2020 11:16    
     7  0920-0002   USR-0920-0001   Hi              18/09/2020 11:52

I'm trying to use this query

SELECT max(id) id , header_id,message from tbl_detail group by header_id

But the result like this

id  header_id   message_from    message         message_date      attachment
 6  0920-0001   ADMIN           MESSAGE ID 1    18/09/2020 11:16    
 7  0920-0002   USR-0920-0001   Hi              18/09/2020 11:52

Did i miss something ? thanks in advance

There is no relationship between the columns in a `SELECT` statement. `MAX(id)` is computed independent of `message`. And while `MAX(id)` is unique for a set of rows having the same `header_id` (i.e. a group), message has different values for different rows. Your query is not valid SQL because of that. MySQL accepts the query until version 5.7.5 but it reserves itself the right to return whatever value it wants for the column `message`. — axiac, Sep 18 '20 at 17:06
You **cannot** select rows using `GROUP BY`. `GROUP BY` **computes** [aggregate values](https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html) for groups of rows. It produces new rows using the data from each group. Take a look at [this answer](https://stackoverflow.com/a/28090544/4265352) on a [similar question](https://stackoverflow.com/q/12102200/4265352). Also read [this answer](https://stackoverflow.com/a/46323030/4265352) for a detailed explanation. — axiac, Sep 18 '20 at 17:07

score 2 · Accepted Answer · answered Sep 18 '20 at 17:06

Your query is invalid standard SQL to start with, because the select and group by clauses are not consistent. MySQL tolerates that, but does not do what you intend (you actually get a arbitrary value for column message).

You want the latest message by header_id: do not think aggregation - think filtering instead.

select d.*
from tbl_detail d
where d.id = (select max(d1.id) from tbl_detail d1 where d1.header_id = d.header_id)

For performance, consider an index on (header_id, id desc).

If you are running MySQL 8.0, this also can be done with window functions:

select d.*
from (
    select d.*, row_number() over(partition by header_id order by id desc) rn
    from tbl_detail d
) d
where rn = 1

Depending on your actual requirement, you might want to use column message_date instead of id to order the rows.

select with MAX() not showing expected result

1 Answers1