Most efficient way to retrieve "top x" records using Rails

Question

We've gotten a report request in, and I'm trying to find out the easiest and most efficient way to pull the numbers.

I have a Deal model with two attributes: _quantity_purchased_ and price. I've been asked to retrieve a list of the top 100 best selling deals in the database. To see how well a deal has done, we multiply the quantity_purchased by the price.

Now, I'm sure I could write something convoluted to get my results, but this seems like such a simple operation, that there has got to be an easier way of doing it. Is there something I could do in MySQL, or using Ruby/Rails? Or am I stuck finding some kind of unpleasant loop?

FYI, we are running Rails 2.3.

Answer 1

You can pass a :limit parameter to your find method to limit the number of results returned. Combined with a :order parameter, you can get the 'top' 100 results:

Deal.find(:all, :order => 'quantity_purchased * price', :limit => 100);

Note as of Rails 3, the correct way of writing this query would be

Deal.order('quantity_purchased * price').limit(100)

Answer 2

class Deal < ActiveRecord::Base
  named_scope :top_deals, :select => 'quantity_purchased * price', :order => '1 DESC', :limit => 100
end

That should do it. In MySQL you can use "ORDER BY 1" to sort by the first column in the select list and so on.