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

Answer 1

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.