How do I join a table with a sub-query containing aggregate functions using Rails 3.0

I have a table StockPrice which contains a timestamp (time), a ticker symbol (symbol) and some other attributes a,b,c etc

In SQL I would write开发者_StackOverflow the following:

Select * 
FROM stock_prices, (SELECT symbol, max(time) as max_time
                    FROM stock_prices
                    GROUP BY symbol) latest_prices
WHERE stock_prices.symbol = latest_prices.symbol
AND stock_prices.time = latest_prices.max_time

I want to express this query using the rails 3.0 ActiveRecord Query stuff select, from, group etc. How do I do that?

While it is possible to do this with a mixture of Rails methods and SQL fragments, this can get messy. It is often much simpler to execute complex queries such as this directly.

Take a look at the find_by_sql method to do this within the context of your model: http://api.rubyonrails.org/classes/ActiveRecord/Base.html#method-c-find_by_sql