User ranking position in a table?

Question

I have 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

score 4 · Accepted Answer · answered Feb 25 '11 at 18:33

4

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)

answered Feb 25 '11 at 18:33

rosscj2533

9,195
7
39
56

Oh that makes sense - if I already know the user id, I don't need to do the sub-select, right? Will this query degrade badly with the more users I get? Say I have 1,000,000 users, and I'm running the query for the last-place user - does mysql have to load all 1,000,000 records to do the count? – user291701 Feb 25 '11 at 19:08
@user291701 - if you already knew the `num_games_won` for the user, you wouldn't need the sub-select. This shouldn't degrade since it only gets a count of records (it won't bring back rows of data for all records). – rosscj2533 Feb 25 '11 at 19:16

score 1 · Answer 2 · edited May 23 '17 at 10:24

1

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.

edited May 23 '17 at 10:24

Community

1
1

answered Feb 25 '11 at 18:33

Daniel DiPaolo

55,313
14
116
115

this solution wouldn't handle ties. – Nathan DeWitt Feb 25 '11 at 18:36
Ties I think would be something handled on the application side of things. This solution still lists the number of games won in descending order, so they in theory, they would be next to each other. – Jacob Saylor Feb 25 '11 at 20:46

User ranking position in a table?

2 Answers2