I have a very basic query to pull the most recent currency FX rates from a table. When I only include prc_curr_cde (currency code) and max(prc_tms) (the most recent date) in the Select statement then it correctly returns approximately 180 records each showing the currency code and most recent date that record was updated. When I include prc_quote_amt (the FX rate I need) in the Select statement then it returns ALL records per currency ignoring the max date. What I need is only the most recent prc_quote_amt by currency code (prc_curr_cde). Any ideas how to change the below to get this right?
Select prc_curr_cde, MAX(prc_tms) as price_date, prc_quote_amt from issue_price
where prc_typ = 'spot'
and prcng_meth_typ = 'currency'
group by prc_curr_cde, prc_quote_amt