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!

---

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

---

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