How to correctly perform group by on max timestamp?-CodePudding

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 |
 ----------- ---------- ----------------------- -------------- ------------ ---------

CodePudding user response：

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;