I have created the below subquery which uses cross join and also uses the := operator to set the variable.

The query is giving below data

+--------------------+-----------------------+---------------
| rnum   | r_id      |  msg       | hostname | createdAt      
+--------------------+-----------------------+---------------
| 1      | faecb001  | test data  | abc01    | 2022-05-23 
| 1      | faecb001  | test1 data | abc01    | 2022-05-22
| 1      | faecb002  | new  data  | abc01    | 2022-05-21
+------------------+-------------------------+---------------

SELECT
    distinct @row_number:= case
        when @RId =  r_id then @row_number + 1
            else 1
        end as rnum,
    @RId:=r_id as r_id,
    msg,
    hostname,
    createdAt
from (
    SELECT
        a.r_id as r_id,
        mta.message as msg,
        tpr.hostname as hostname,
        trw.retry as rrtry,
        a.createdAt as createdAt
    FROM sa.nra a
    JOIN sa.nmta mta ON a.a_mid = mta.id
    JOIN sa.treq tpr ON tpr.n_r_id = a.r_id
    JOIN sa.twflw trw ON trw.astp = mta.stp AND tpr.p_w_id = trw.id
    WHERE mta.rstatus in ('FAIL')
    ORDER BY  a.r_id
) as  sa
CROSS JOIN (SELECT @row_number := 0) AS vars
HAVING rnum = 1
ORDER BY createdAt DESC;

I want to update the query so that is shows the latest record(based upon date) for a particular r_id and hostname.

Desired Output:

+--------------------+-----------------------+---------------
| rnum   | r_id      |  msg       | hostname | createdAt      
+--------------------+-----------------------+---------------
| 1      | faecb001  | test data  | abc01    | 2022-05-23 
| 1      | faecb002  | new  data  | abc01    | 2022-05-21
+------------------+-------------------------+---------------