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
– The Impaler, Commented May 2, 2022 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
– meallhour, Commented May 2, 2022 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 — Bill Karwin
– Bill Karwin, Commented May 2, 2022 at 20:22
I am with Bill here. Why are you using such an old MySQL version? Such things are much easier in MySQL 8. — Thorsten Kettner
– Thorsten Kettner, Commented May 2, 2022 at 20:30

Barmar · Accepted Answer · 2022-05-02 20:22:44Z

3

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.

edited May 2, 2022 at 20:22

answered May 2, 2022 at 20:18

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

meallhour Over a year ago

I have added createdAt in my original query. Sorry I missed it earlier

philipxy Over a year ago

This is undefined behaviour, see my comments on the question.

Collectives™ on Stack Overflow

Replace mysql variables with Join

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related