How do I write a Rails finder method that will return the greatest date grouped by record?

Question

I'm using Rails 5 with PostGres 9.5. I have a table that tracks prices ...

                                         Table "public.crypto_prices"
       Column       |            Type             |                         Modifiers
--------------------+-----------------------------+------------------------------------------------------------
 id                 | integer                     | not null default nextval('crypto_prices_id_seq'::regclass)
 crypto_currency_id | integer                     |
 market_cap_usd     | bigint                      |
 total_supply       | bigint                      |
 last_updated       | timestamp without time zone |
 created_at         | timestamp without time zone | not null
 updated_at         | timestamp without time zone | not null

I would like to get the latest price per currency (where last_updated is greatest) for a select currencies. I can find all the prices related to certain currencies like so

current_prices = CryptoPrice.where(crypto_currency_id: CryptoIndexCurrency.all.pluck(:crypto_currency_id).uniq)

Then I can sort them by currency into arrays, looping through each until I find the one with the greatest last_updated value, but how can I write a finder that will return exactly one row per currency with the greatest last_updated date?

Edit: Tried Owl Max's suggestion like so

ids = CryptoIndexCurrency.all.pluck(:crypto_currency_id).uniq
crypto_price_ids = CryptoPrice.where(crypto_currency_id: ids).group(:crypto_currency_id).maximum(:last_updated).keys
puts "price ids: #{crypto_price_ids.length}"
@crypto_prices = CryptoPrice.where(crypto_currency_id: crypto_price_ids)
puts "ids: #{@crypto_prices.size}"

Although the first "puts" only reveals a size of "12" the second puts reveals over 38,000 results. It should only be returning 12 results, one for each currency.

Answer 1

We can write a finder that will return exactly one row per currency with the greatest last_updated date in such a way like

current_prices = CryptoPrice.where(crypto_currency_id: CryptoIndexCurrency.all.pluck(:crypto_currency_id).uniq).select("*, id as crypto_price_id, MAX(last_updated) as last_updated").group(:crypto_currency_id)

I hope that this will took you closer to your goal. Thank you.