-1

I am using MySQL 5-enterprise-commercial version.

Sample Data

+-----------+----------+------------+---------------
| r_id      | hostname | status     |  createdAt   |
+-----------+-----------------------+---------------
| faecb001  | abc01    | FAILR1     | 2022-05-23   |
| faecb001  | abc01    | FAILR2     | 2022-05-22   |
| faecb002  | abc01    | FAILR2     | 2022-05-21   |
+-----------+----------+------------+---------------

I have created the below query which gives above sample data.

SELECT
   audit.r_id as r_id,
   nhost.host as hostname,
   meta.r_status as status,   
   audit.createdAt as createdAt
FROM audit
   JOIN npr ON npr.nr_id = audit.r_id
   AND audit.createdAt BETWEEN now() - interval 90 DAY AND now()
   JOIN meta ON audit.audit_meta_id = meta.id 
   JOIN nhost ON npr.nr_id = nhost.nr_id              
   WHERE meta.r_status regexp 'FAIL'   
ORDER BY  audit.createdAt DESC;

I want to update the query so that is shows the latest record(based upon date) for every group of r_id and hostname. I have written the below query.

SELECT
   audit.r_id as r_id,
   nhost.host as hostname,
   meta.r_status as status,   
   audit.createdAt as createdAt
FROM audit
   JOIN npr ON npr.nr_id = audit.r_id
   AND audit.createdAt BETWEEN now() - interval 90 DAY AND now()
   AND audit.a_meta_id = (select a_meta_id from audit
                                  where npr.nr_id = audit.r_id                                  
                                  order by audit.createdAt limit 1)
   JOIN meta ON audit.audit_meta_id = meta.id 
   JOIN nhost ON npr.nr_id = nhost.nr_id              
   WHERE meta.r_status regexp 'FAIL'   
ORDER BY  audit.createdAt DESC;

The above uddated query is taking too much time to run and then failing with error lost connection to mysql server.

Desired Output:

+-----------+----------+------------+---------------
| r_id      | hostname | status     |  createdAt   |
+-----------+-----------------------+---------------
| faecb001  | abc01    | FAILR1     | 2022-05-23   |
| faecb002  | abc01    | FAILR2     | 2022-05-21   |
+-----------+----------+------------+---------------
meallhour
  • 11,517
  • 14
  • 47
  • 86
  • Re the last version, assigning & reading the same user variable in the same select statement is explicitly undefined behaviour, see the manual on those topics. – philipxy Jun 03 '22 at 00:17
  • Please in code questions give a [mre]--cut & paste & runnable code & example input; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL include DDL & tabular initialization code. For debug that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. [ask] [Help] When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. (Debugging fundamental.) – philipxy Jun 03 '22 at 00:19
  • Suggest you begin with an explicit value instead of now() in composing/debugging. – philipxy Jun 03 '22 at 00:24

0 Answers0