Ruby on Rails calculating "rank" based upon database values?

Question

Is there an easy and fast way to calculate the rank of a field in a database using Ruby on Rails? For example, if I have a math_scores table, and would like to find a given a MathScore.find(:all, :condtions => ... :order =>...) then iterate through all of them to find out where the test score falls, but there's got to be a more straightforward way... Any advice?

Here's some info on the schema, it's just a simple table:

first_name varchar(50)

last_name varchar(50)

test_id int

score float

Clarification: I guess by question is closer to how would I retrieve the rank value when doing: rank = MathScore.find_by_sql("select count(*) as rank from (select * from math_scores where score > (select score from high_scores where test_id = 33 AND first_name = 'John' AND last_name = 'Doe') order by score desc) as s")

I get [#<HighScore:0x6ca4724 @attributes={"rank"=>"3"}>]:Array based upon the query, but how do I get at the rank value?

Thanks in advance, Ben

Answer 1

I don't particularly know Rails, but I'm assuming there is some equivalent for the following SQL example.

SELECT COUNT(*) + 1 of all the rows that would come before the row in question.

For instance, to get the rank of an item whose score you know is 75.0:

SELECT COUNT(*) + 1 as [rank] from my_table
    WHERE score > 75.0

If you don't know the score of the item, but you know other identifying information:

SELECT COUNT(*) + 1 as [rank] from my_table
    WHERE score > (SELECT score FROM my_table WHERE test_id = 123)

This has the side effect of assigning the same rank to tests that have the same score, but still distributing it right. So you might have one score at rank 1, then two scores tied for rank 2, then the next would have rank 4, etc. If you don't like this side effect, you can get fancy with your WHERE clause to split them by test_id or first and last name.

This is only an appropriate solution for one test at a time. If you want to rank them all, you may as well fetch them all, ORDER BY score, and rank them yourself.

Answer 2

Are you trying to determine where in the bell curve a test score falls? You might try rolling your own function for the model by using one of MySQL's built in group by functions like STDDEV_POP(). You can see them here. With some fancy footwork, you can return the grade's position on the curve with one call to the db.

Answer 3

You can use named scopes with Rails 2.2 to help with this, eg.

class MathScore
  named_scope :passed, :conditions => {:score => 60..100 }
  named_scope :failed, :conditions => {:score => 0..59}
end

Which would allow you to do the following in a controller:

@passing_scores = MathScore.passed
@failing_scores = MathScore.failed

So you can iterate through them in a view.

---

In response to your clarification (in erb):

<% your_array.each do |hs| %>
  <%= hs.rank %>
<% end %>

Answer 4

sql = "select count(*) as rank from (select * from math_scores where score > (select score from high_scores where test_id = 33 AND first_name = 'John' AND last_name = 'Doe') order by score desc) as s"
rank = MathScore.find_by_sql(sql)[0].rank

Answer 5

Not sure what DBMS you have; but most support rank and dense_rank SQL functions (windowing functions). Example:

SQL> select ename,sal,
  2   row_number()
  3     over (order by sal desc)rn,
  4   rank()
  5     over (order by sal desc)rnk,
  6   dense_rank()
  7     over (order by sal desc)drnk
  8   from emp
  9  order by sal desc
 10  /

ENAME    SAL   RN   RNK   DRNK
-----   ----   --   ---   ----
 KING   5000    1     1      1
 FORD   3000    2     2      2
SCOTT   3000    3     2      2
JONES   2975    4     4      3
BLAKE   2850    5     5      4
CLARK   2450    6     6      5