CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3
BeachRunnerFred
BeachRunnerFred

Reputation: 18578

Rails 3: How can I find the user(s) with the highest score?

I'm diving into Rails and I'm learning how to use ActiveRecord and I've created a Player model that has a score attribute. I'm trying to figure out an efficient way to to query the DB for the player(s) that has the highest score. In cases where the two or more players are tied for the highest score, I'll need all the players. I can figure out how to do this by sorting the list first, but I wasn't sure if that's necessary. What the most efficient way to retrieve a set of players with the highest score?

Also, would creating an index in DB for the score column improve the speed of this query?

Thanks so much for your wisdom!

Upvotes: 1

Views: 700

Answers (2)

Mitch Dempsey
Mitch Dempsey

Reputation: 39929

Player.order("score DESC").limit(50)

Should be simple as that. Have a look at the rails guides.

Or better yet, do something like this:

high_score = Player.maximum(:score)
high_score_players = Player.where(:score => high_score).all

Upvotes: 1

tadman
tadman

Reputation: 211670

If you're querying on a column as part of a WHERE or ORDER BY you should almost always have an index. Without an index your database must do a table scan, and every single one of these is expensive.

Capturing the player or players with the highest score can be done in a two-pass operation, first to determine the highest score, second to fetch all players with that score:

@players = Player.where("score=(SELECT MAX(score) FROM #{Player.table_name}").all

Upvotes: 1

Related Questions