RAILS 4 ACTIVERECORD Retrieve Newest Records by Key
I have a table in my DB (PostgreSQL) that looks something like this:
Currency Rate Created_at
USD 13.5 08/07/2014
EURO 18.5 08/07/2014
USD 13.3 08/08/2014
EURO 18.3 08/08/2014
USD 13.4 08/06/2014
EURO 18.4 08/06/2014
USD 13.7 08/05/2014
EURO 18.7 08/05/2014
What I want to do is get only the newest values for each currency key ("USD", "EURO") In this example, the expected result would be:
USD 13.3 08/08/2014
EURO 18.3 08/08/2014
What I have tried:
BancomerTasa.order(:created_at).select("DISTINCT ON (divisa) *")
ActiveRecord::StatementInvalid: PG::InvalidColumnReference:
ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions
(ERROR)
BancomerTasa.uniq.pluck(:divisa, :tasa)
(1.1ms) SELECT DISTINCT "bancomer_tasas"."divisa"
, "bancomer_tasas"."tasa" FROM "bancomer_tasas"
=> [["EURO", #<BigDecimal:7fd4941d66c0,'0.1812E2',18(18)>],
["DOLAR", #<BigDecimal:7fd4941d64e0,'0.1352E2',18(18)>],
["DOLAR", #<BigDecimal:7fd4941d62d8,'0.1356E2',18(18)>],
["EURO", #<BigDecimal:7fd4941d6148,'0.1814E2',18(18)>]]
(TOO MANY VALUES)
BancomerTasa.group(:id,:divisa).having('created_at = MAX(created_at)')
(GROUPING BY ID MAKES NO SENSE TO ME SINCE THAT THE PK
BUT IF I DON'T I GET THIS ERROR:
PG::GroupingError: ERROR: column "bancomer_tasas.id"
must appear in the GROUP BY clause or be used in an aggregate function)
I think I'm a little confused now. Any help would be greatly appreciated!
+3
source to share
1 answer
To get the most recent entry BancomerTasa
for each currency, use where
to add a condition for the correct currency, order
by created_at and get the entry last
:
most_recent_usd = BancomerTasa.where(currency: 'USD').order(:created_at).last
most_recent_euro = BancomerTasa.where(currency: 'EURO').order(:created_at).last
+1
source to share