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 wallet.id from wallet where wallet.user_id = 1 )) AND
division_user.wallet_id = wallet.id
group by wallet.id
order by wallet.weekly_profit_value DESC


The output of the query is:

1,3
2,5
3,1


Desired result

1,1
2,2
3,3

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 = w.id
      where du.division_id in (select du2.division_id 
                               from division_user du2
                               where du2.wallet_id in (select w2.id
                                                       from wallet w2
                                                       where w2.user_id = 1
                                                      )
                              ) 
     group by w.id 
    ) duw cross join
    (SELECT @curRow := 0) params
order by w.weekly_profit_value DESC