CODEWITHSUNDEEP
mysqlsqlrow
Renan Geraldo
Renan Geraldo

Reputation: 665

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

Upvotes: 0

Views: 76

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

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

Upvotes: 1

Related Questions