Replace mysql variables with Join

Question

I am using MySQL 5-enterprise-commercial version. I have written the below 2 queries which I want to convert into a single query using join.

SET @row_number :=0;
SELECT
       @row_number:= case
            when @RId =  r_id then @row_number + 1
                else 1
            end as rnum,
        @RId:=r_id as r_id,
        msg,
        supplier,
        rrtry from (
            SELECT
                a.r_id as r_id,
                mta.message as msg,
                tpr.supplier as supplier,
                trw.retry as rrtry,
                a.createdAt as createdAt,
            FROM sa.nra a,
                sa.nmta mta,
                sa.treq tpr,
                sa.twflw trw
            WHERE tpr.n_r_id = a.r_id
                AND trw.astp = mta.stp
                AND mta.rstatus in ('FAIL')
                AND tpr.p_w_id = trw.id
                AND a.a_mid = mta.id
                AND a.createdAt  BETWEEN now() - interval 30 DAY AND now() 
            ORDER BY  a.r_id
) as  sa
HAVING rnum = 1
ORDER BY createdAt DESC;

Basically, there are multiple entries for r_id, and I want to get the latest entry for a particular r_id and that is why I am using ORDER BY a_createdAt DESC

Please add a few rows of sample data and the expected result. — The Impaler, May 02 '22 at 20:04
The first query is `SET @row_number :=0;` and second one has `select clause`. I want to get rid of using `@row_number` and want the same result without use of `@row_number` variable — meallhour, May 02 '22 at 20:12
Can you not upgrade to MySQL 8.0 so you can use window functions for the row numbering? That would be easier. See example in my answer to [Retrieving the last record in each group - MySQL](https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql/1313293#1313293) — Bill Karwin, May 02 '22 at 20:22
@meallhour-- ok I see. Usually this is considered 1 query with a sub-query — Hogan, May 02 '22 at 20:28
I am with Bill here. Why are you using such an old MySQL version? Such things are much easier in MySQL 8. — Thorsten Kettner, May 02 '22 at 20:30
Assigning & reading the same variable in the same select statement is explicitly undefined behaviour. (The effect of an assignment in a select isn't even clearly defined.) So not only does this not have any guaranteed behaviour it doesn't communicate to us what you want, you are assuming an interpretation of the code that you don't give, like some order of evaluation. (Before 8.0 & window functions one had to write a stored procedure to do such a sequence of assignments.) PS Please clarify via edits, not comments. — philipxy, May 02 '22 at 23:38
[Why the order of evaluation for expressions involving user variables is undefined?](https://stackoverflow.com/a/44751302/3404097) See my comments (re an incorrect answer) at [MySQL - Define a variable within select and use it within the same select](https://stackoverflow.com/a/16715618/3404097). — philipxy, May 02 '22 at 23:45

Barmar · Accepted Answer · 2022-05-02T20:22:44.757

Join the query with a subquery that uses the := operator to set the variable.

SELECT
    @row_number:= case
        when @RId =  r_id then @row_number + 1
            else 1
        end as rnum,
    @RId:=r_id as r_id,
    msg,
    supplier,
    rrtry 
from (
    SELECT
        a.r_id as r_id,
        mta.message as msg,
        tpr.supplier as supplier,
        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 a_createdAt DESC;

I've replaced the cross-product in the subquery with ANSI JOIN.

If you're using MySQL 8.0, you can use the RANK() or ROW_NUMBER() window functions instead of a user variable.

I have added `createdAt` in my original query. Sorry I missed it earlier — meallhour, May 02 '22 at 20:22
This is undefined behaviour, see my comments on the question. — philipxy, May 02 '22 at 23:48

Replace mysql variables with Join

1 Answers1