Max date query returning all records instead of most recent records

Transact-SQL
1

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

2

try this: (excuse the typos, but I don't have the schema you do)

select p.prc_curr_cde, p.Price_date, i.prc_quote_amt
from (
Select prc_curr_cde, MAX(prc_tms) as price_date from issue_price
where prc_typ = 'spot'
and prcng_meth_typ = 'currency'
group by prc_curr_cde) p
join issue_price i
on i.prc_curr_cde = p.prc_curr_cde
and i.price_date = p.price_date

3

You can use row_number to identify the current row.

  With issuePrice
    As (
Select prc_curr_cde
     , price_date = prc_tms
     , prc_quote_amt
     , rn = row_number() over(partition by prc_curr_cde order by prc_tms desc)
       )
Select *
  From issuePrice
 Where rn = 1;
1 Like