mysql内部加入和动态排名计算错误＃1241

Question

i am trying to calculate the rank of the user in my portal... i am joining user name from the user_table_name to the rank_table and calculating there rank dynamically if i run the query individually it runs fine but when i use it together there is a #1241 error here is mycode

SELECT (SELECT utn.name, rt.amount_left, rt.loan_amount, rt.currency_amount,
               rt.stock_amount, rt.fd_amount, rt.user_money
        FROM `user_table_name` AS utn INNER JOIN
             `rank_table` AS rt on rt.user_id=utn.userid
       ) AS ut, 
       (SELECT  COUNT(*)
        FROM    rank_table ui
        WHERE   (ui.amount_left) >= (uo.amount_left)
       ) AS rank 
FROM    rank_table uo
WHERE   1
ORDER BY rank ASC  

Thanks Gordon for below query

SELECT utn.name, rt.amount_left, rt.loan_amount, rt.currency_amount,
   rt.stock_amount, rt.fd_amount, rt.user_money,
   (SELECT  COUNT(*)
     FROM    rank_table ui
     WHERE   (ui.amount_left) >= (rt.amount_left)
   ) AS rank
FROM `user_table_name` utn INNER JOIN
 `rank_table` rt
  on rt.user_id = utn.userid
ORDER BY rank ASC;

but the query gives me weird results such as if the 10 and 11 position user have same point then there rank is shows as 11 not 10..so can you please help

Answer 1

A subquery to a select can only return one column. Not a bunch of them. I think you want something like this:

SELECT utn.name, rt.amount_left, rt.loan_amount, rt.currency_amount,
       rt.stock_amount, rt.fd_amount, rt.user_money,
       (SELECT  COUNT(*)
         FROM    rank_table ui
         WHERE   (ui.amount_left) >= (rt.amount_left)
       ) AS rank
FROM `user_table_name` utn INNER JOIN
     `rank_table` rt
      on rt.user_id = utn.userid
ORDER BY rank ASC;