Fetching Minimum/Maximum for each group in ActiveRecord

Question

This is an age-old question where given a table with attributes 'type', 'variety' and 'price', that you fetch the record with the minimum price for each type there is.

In SQL, we can do this by:

select f.type, f.variety, f.price   
from (  select type, min(price) as minprice from table group by type ) as x  
inner join table as f on f.type = x.type and f.price = x.minprice;`

We could perhaps imitate this by:

minprices = Table.minimum(:price, :group => type)  
result = []
minprices.each_pair do |t, p|  
   result << Table.find(:first, :conditions => ["type = ? and price = ?", t, p])
end

Is there a better implementation than this?

Answer 1

Table.minimum(:price, :group => :type)

See http://api.rubyonrails.org/classes/ActiveRecord/Calculations.html#method-i-minimum for more.