How to correctly perform group by on max timestamp?

Question

I am using MySQL 5.7.38 version. I have the below query which is returning the correct sample data as below:

SELECT
   t.r_id,
   t.hostname,
   t.a_tmstamp,
   max(t.r_status) as r_status,
   max(t.message) as message,
   max(t.m_id) as m_id
FROM (
SELECT
   audit.r_id as r_id,
   nhost.host_name as hostname,
   meta.r_status as r_status,   
   meta.step as step,
   meta.id as m_id,
   meta.message as message,
   audit.a_timestamp as a_tmstamp,
   npr.nas_provider as nas_provider             
FROM audit
   INNER JOIN npr ON npr.nr_id = audit.r_id
   AND audit.a_timestamp BETWEEN now() - interval 30 DAY AND now()
   INNER JOIN nhost ON audit.r_id = nhost.nr_id      
   INNER JOIN meta ON audit.audit_m_id = meta.id                                  
   INNER JOIN nprw ON npr.pw_id = nprw.id   
   AND nprw.ap_step = meta.step                
   WHERE meta.r_status regexp 'FAIL'
   ORDER BY  a_timestamp DESC
     ) AS t 
 GROUP BY t.r_id, t.hostname, t.a_tmstamp
 ORDER BY a_tmstamp DESC;

Sample Data:

+-----------+----------+-----------------------+--------------+------------+---------+
| r_id      | hostname | a_tmstamp             |  r_status    |  message   |  m_id   |
+-----------+----------------------------------+--------------+------------+---------+
| a1ffadc9  | abc01    | 6/9/2022  6:15:17 PM  | PROGRESS     |  message1  | d759df3 |
| a1ffadc9  | abc02    | 6/9/2022  6:15:17 PM  | PROGRESS     |  message2  | d759df3 |
| a1ffadc9  | abc01    | 6/9/2022  6:14:17 PM  | PROGRESS     |  null      | fdb2f19 |
| a1ffadc9  | abc02    | 6/9/2022  6:14:17 PM  | PROGRESS     |  null      | fdb2f19 |
| 246cc315  | cad01    | 6/9/2022  4:43:52 PM  | FAILED       |  message3  | f5435f0 |
| 246cc315  | cad02    | 6/9/2022  4:43:52 PM  | FAILED       |  message4  | f5435f0 |
| 258e55ac  | cad01    | 6/9/2022  4:42:52 PM  | FAILED       |  reason1   | 432ca62 |
| 258e55ac  | cad02    | 6/9/2022  4:42:52 PM  | FAILED       |  reason2   | 432ca62 |
+-----------+----------+-----------------------+--------------+------------+---------+

How can I perform group by on max(t.a_tmstamp) so that I get below output?

Desired Output:

+-----------+----------+-----------------------+--------------+------------+---------+
| r_id      | hostname | a_tmstamp             |  r_status    |  message   |  m_id   |
+-----------+----------------------------------+--------------+------------+---------+
| a1ffadc9  | abc01    | 6/9/2022  6:15:17 PM  | PROGRESS     |  message1  | d759df3 |
| a1ffadc9  | abc02    | 6/9/2022  6:15:17 PM  | PROGRESS     |  message2  | d759df3 |
| 246cc315  | cad01    | 6/9/2022  4:43:52 PM  | FAILED       |  message3  | f5435f0 |
| 246cc315  | cad02    | 6/9/2022  4:43:52 PM  | FAILED       |  message4  | f5435f0 |
+-----------+----------+-----------------------+--------------+------------+---------+

unclear what version you mean; what does `select version();` show? — ysth, Jun 10 '22 at 00:19
Are you sure the query return that result? It doesn't seem that `GROUP BY t.r_id, t.hostname, t.a_tmstamp` will give that kind of result though.. — FanoFN, Jun 10 '22 at 00:29
Yes, I have the query and output in front of me. the above query is returning the given sample data. — meallhour, Jun 10 '22 at 00:35
@FanoFN they want only the record for each r_id/hostname with the latest a_tmstamp — ysth, Jun 10 '22 at 01:23
Does this answer your question? [Fetch the row which has the Max value for a column](https://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column) — philipxy, Jun 10 '22 at 03:54

score 2 · Accepted Answer · answered Jun 10 '22 at 01:28

You can employ some trickery to get the status/message/m_id for the latest a_tmpstamp:

SELECT
   t.r_id,
   t.hostname,
   max(t.a_tmstamp) max_tmstamp,
   substr(max(concat(t.a_tmstamp,t.r_status)),24) as r_status,
   substr(max(concat(t.a_tmstamp,t.message)),24) as message,
   substr(max(concat(t.a_tmstamp,t.m_id)),24) as m_id
FROM (
SELECT
   audit.r_id as r_id,
   nhost.host_name as hostname,
   meta.r_status as r_status,   
   meta.step as step,
   meta.id as m_id,
   meta.message as message,
   audit.a_timestamp as a_tmstamp,
   npr.nas_provider as nas_provider             
FROM audit
   INNER JOIN npr ON npr.nr_id = audit.r_id
   AND audit.a_timestamp BETWEEN now() - interval 30 DAY AND now()
   INNER JOIN nhost ON audit.r_id = nhost.nr_id      
   INNER JOIN meta ON audit.audit_m_id = meta.id                                  
   INNER JOIN nprw ON npr.pw_id = nprw.id   
   AND nprw.ap_step = meta.step                
   WHERE meta.r_status regexp 'FAIL'
   ORDER BY  a_timestamp DESC
     ) AS t 
 GROUP BY t.r_id, t.hostname
 ORDER BY max_tmstamp DESC;

thanks a lot. what do you think about using `substring_index(group_concat(t.message order by t.a_tmstamp desc), ',', 1) as message,`? — meallhour, Jun 10 '22 at 01:59
doesn't work if message contains a comma or is longer than the group_concat_max_len setting (and gives a warning if it is too long); the timestamp is a fixed length, easier just to concat and then remove it. I guess if you might change the type of the timestamp, you could use `length(max(t.a_tmstamp))+1` instead of 24 — ysth, Jun 10 '22 at 05:11

How to correctly perform group by on max timestamp?

1 Answers1