Renan Geraldo Renan Geraldo - 1 month ago 12
MySQL Question

MySQL ranking query is not giving expected value

I have the following situation: There is a table that relates wallets and divisions. I have to rank users that are in the same division. The SQL code that I am using is working, but when I try put row numbers it gives strange results. I really tried a lot of different strategies but nothing worked for me.

select * , @curRow := @curRow + 1 AS row_number
from division_user,
wallet JOIN (SELECT @curRow := 0) r
where division_user.division_id in (select division_user.division_id
from division_user
where division_user.wallet_id in
(select from wallet where wallet.user_id = 1 )) AND
division_user.wallet_id =
group by
order by wallet.weekly_profit_value DESC

The output of the query is:


Desired result


Answer Source

Variables and group by don't always work together. Also, never use commas in the from clause. Always use proper, explicit join syntax.

select duw.*, (@curRow := @curRow + 1) AS row_number
from (select du.*, w.*
      from division_user du join 
           wallet w 
           on du.wallet_id =
      where du.division_id in (select du2.division_id 
                               from division_user du2
                               where du2.wallet_id in (select
                                                       from wallet w2
                                                       where w2.user_id = 1
     group by 
    ) duw cross join
    (SELECT @curRow := 0) params
order by w.weekly_profit_value DESC