User ranking position in a table?

I have开发者_JS百科 a game application, I'm keeping track of the number of games won per user, something like:

// table: users
id | username | num_games_won

How would I tell a user their overall ranking in terms of num_games_won? For example:

   id   | username | num_games_won
-----------------------------------
  723      john          203              
  724      mary          1924
  725      steve         391

The rankings would be: mary->0, steve->1, john->2. Given a username, how would I find their ranking number? (I'm using mysql)

Thanks

Try counting the number of users that have more games won that the user you are interested in (by id or username)

SELECT COUNT(*) FROM users 
WHERE num_games_won > (SELECT num_games_won FROM users WHERE id = 723)

In this case you can do a simple ORDER BY. Unfortunately, MySQL doesn't support the nice analytical functions like RANK or ROWNUMBER that other databases support, because those would be other potential solutions when the answer isn't as simple as ORDER BY.

(edit: you can sort of cheat and simulate ROWNUMBER in MySQL thanks to this answer on SO)

In this case, you'd do SELECT * FROM users ORDER BY num_games_won DESC and the first row would have the most, the second would have second most, etc.