Easy Ranking with Rails and MySQL

As part of some of the new features I'm adding to Sphericle, I needed to add a ranking system for user accounts, based on a point system. Specifically, I needed to write a method for my User class (on the Sphericle server, which is a Rails app) that would give me the ranking for a User, based on how many points that User has compared to all the other Users.

Writing this SQL query was slightly outside of what I normally have to do, so rather than think too hard, I googled, and found this article which pretty much gave me the exact query I needed. I wrapped that into a method of my User class, and now it is presented here, for your benefit:

def ranking
  User.connection.select_value("SELECT COUNT(*)+1 AS ranking FROM users "+
    "WHERE points > "+
      "(SELECT points FROM users WHERE id = #{self.id})").to_i
end

This method will return an integer from 1 (1st place) to whatever last place is, based on your User count. The SQL uses a sub-select query that basically asks, "how many users are there that have more points than this user has?" and then adds 1 to the result (so if there are 0 users with more points than this user, you are in place 1, not 0).

For a more in-depth discussion of the query, read the article linked to above, as well as the response to it.