How do I do this more complex query in Rails?

Question

Not understanding how to express a particular type of query with ActiveRecord's syntax.

Company 
has_many shareprices

• When the processes run to update the shareprices, a new entry is created in the shareprice table. So you obviously end up with lots of shareprice rows for each company. In such a scenario you could end up with 1 company having 5 very high share price entries in the last time period.

So let's say I want to return the companies that currently have the highest shareprice in the last time period - I need it to basically return the max price for that company from the shareprices table and then find the next.

• I can't work out how to do that with ActiveRecord syntax. I've tried a lot of approaches inspired by other stackoverflow answers but invariably can't get it to return unique companies so I'm missing a point somewhere around select unique, joining, group by, or something else.

Environment: postgresSQL backend deploying to heroku.

Help very much appreciated.

Answer 1

Without knowing your structure it's hard to provide a concrete answer. But somthing like this should work.

Company.select('max(share_prices.price) as price,companies.id').joins(:share_prices).group(:id).order('price')