MAX function

[toray]

I am trying to extract the latest date for a currency rate located in a oracle database table.
The data in the table includes many entries for each currency code. For example the Euro has 12 entries ie 12 dates,and I need the last date entry to get the current rate.
The MAX function works find until I add the numeric rate.ie I get all the currency rates.
How do i associate the currency rate to the MAX date?

 

[Dagon]

You probably need to look at WINDOWING functions e.g. RANK, ROW_NUMBER. These enable you not just to get the highest value but the other columns on that row.

The query would look something like:

select currency, rate, rate_date
from
(select currency,
        rate, 
        rate_date,
        rank() over (partition by currency order by rate_date desc) as rn
from currencies)
where rn=1