Players current rank

def best_drivers(limit = 10)
  lost  = 'SELECT COUNT(c.id) FROM challenges AS c
           WHERE (c.challenger_id = u.id AND c.challenge_state_id = 5)
           OR (c.opponent_id = u.id AND c.challenge_state_id = 4)'

  won   = 'SELECT COUNT(c.id) FROM challenges AS c
           WHERE (c.challenger_id = u.id AND c.challenge_state_id = 4)
           OR (c.opponent_id = u.id AND c.challenge_state_id = 5)'

  # WHERE statement prevents division by zero
  find_by_sql(
    'SELECT u.*, cast(('+won+') as float)/(cast(('+won+') as float)+cast(('+lost+') as float)) AS win_ratio, ('+won+') AS won, ('+lost+') AS lost
     FROM users AS u
     WHERE ('+won+') > 0
     ORDER BY win_ratio DESC, won DESC
     LIMIT '+limit.to_s
  )
end

I'm using this query to sort the users in the ranking table, it works fine.

N开发者_Python百科ow I need a query that will tell me the current rank of a user in the show view. Is it possible?

You could add a "rank" to your query and use it as sub-select. In the surrounding query use where to get your user:

select * from 
   (select @rownum:=@rownum+1 ‘rank’, YOUR_QUERY from YOUR_TABLE, (SELECT @rownum:=0) r) e 
where user_id = 1;

Consider caching the rank of your user because subquerys are "expensive" (take long time to run/need much memory).

Please, try google before you ask, e.g. "mysql select player rank" results in this: Fast mySQL leaderboard with player rank (& surrounding players) which pretty much answers your question.

However, the task you want to do is very expensive. If you have a lot of items in the table I'd consider to denormalize the rank and recount it for the whole table every time a user's score changes.