How to merge two related joins in one?

user_6381081 0
Viewed 57

I have a query like this:

select * from wallet w
join where_to_pay wtp on wtp.wallet_id = w.id
join (select wallet_id w_id, max(business_id) from where_to_pay group by wallet_id) on w_id = w.id

As you can see, there is two joins between wallet and where_to_pay tables. The only different is, in the second join, the rows are grouped by wallet_id (which returns an unique business_id).

Since those two joins are similar, I want to know, is it possible to remove one of them and use a window function (such as row_number or first_row or what's needed) instead?

join mysql sql
1 Answers

If two tables columns are needed so use subquery where ROW_NUMBER() is used for serializing wallet_id wise info with descending order business_id. Then JOIN with main table.

SELECT * 
FROM wallet w
INNER JOIN (SELECT *
                 , ROW_NUMBER() OVER (PARTITION BY wallet_id ORDER BY business_id DESC) row_num
            FROM where_to_pay) t
        ON w_id = t.wallet_id
       AND t.row_num = 1;
user_163646250
Related