Rails 3 - How to get line number from model with order

Question

I am building a small application that has a concept of leaders. Basically, a model is just the name of the player and current_score.

what I want to do is get the rating of a specific player, and given that new points come all the time, he must be dynamic. Obviously, I could just do the usual find with the order by clause, and then I would have to iterate over each record. Not very effective when I could have 100,000 rows.

Any suggestions on which approach I should take?

Here is the migration for the table:

class CreateScores < ActiveRecord::Migration def self.up create_table :scores do |t| t.string :player_name t.integer :current_score t.timestamps end end def self.down drop_table :scores end end 

EDIT As an example, I have the following:

 Score.select('player_name, current_score').limit(20) => [#<Score player_name: "Keith Hughes", current_score: 9>, #<Score player_name: "Diane Chapman", current_score: 8>, #<Score player_name: "Helen Dixon", current_score: 4>, #<Score player_name: "Donald Lynch", current_score: 9>, #<Score player_name: "Shawn Snyder", current_score: 2>, #<Score player_name: "Nancy Palmer", current_score: 9>, #<Score player_name: "Janet Arnold", current_score: 1>, #<Score player_name: "Sharon Torres", current_score: 9>, #<Score player_name: "Keith Ortiz", current_score: 5>, #<Score player_name: "Judith Day", current_score: 3>, #<Score player_name: "Gregory Watson", current_score: 7>, #<Score player_name: "Jeremy Welch", current_score: 3>, #<Score player_name: "Sharon Oliver", current_score: 7>, #<Score player_name: "Donald Lewis", current_score: 7>, #<Score player_name: "Timothy Frazier", current_score: 7>, #<Score player_name: "John Richards", current_score: 1>, #<Score player_name: "Carolyn White", current_score: 4>, #<Score player_name: "Ronald Smith", current_score: 2>, #<Score player_name: "Emily Freeman", current_score: 9>, #<Score player_name: "Gregory Wright", current_score: 2>] 

How do I rate Donald Lewis?

Answer 1

You can count the number of records with a higher current_score value. You still have to do this per record (or execute a subquery in your sql).

 class Score < ActiveRecord::Base def ranking Score.count(:conditions => ['current_score > ?', self.current_score]) end end 

Answer 2

 Score.find(:select => "player_name,max(current_score) as high_score",:group_by => "player_name",:order => "max(current_score) DESC")