title: mysql inner joining & dynamic rank calculation error #1241
body: <p>i am trying to calculate the rank of the user in my portal... i am joining user name from the <code>user_table_name</code> to the <code>rank_table</code> 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</p>
  
  <pre><code>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) &gt;= (uo.amount_left)
         ) AS rank 
  FROM    rank_table uo
  WHERE   1
  ORDER BY rank ASC  
  </code></pre>
  
  <p>Thanks Gordon for below query</p>
  
  <pre><code>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) &gt;= (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;
  </code></pre>
  
  <p>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</p>