Latest Record 1

[NotSQL]

Hi,

I’m having problems getting the latest record/records from my table.

The scenario is: I have a currency table with the exchange rate based on a certain date. What i want to be able to do is select the distinct Currency code based on the max date, is this possible? Or is there a better way?

Table looks like this

CurrencyCode Date Rate
Dollar 01-03-2009 1.4
Dollar 03-04-2009 1.32
Dollar 04-05-2009 1.36
Krone 02-03-2009 10.34
Krone 06-04-2009 10.42
Krone 10-05-2009 9.43
Aus 21-03-2009 2.54
Aus 14-04-2009 2.11
Aus 06-05-2009 2.35

 

[NotSQL]

Would i use

Select max(date) currencyCode, Rate
From Table1
Group by CurrencyCode, Rate

 

[r937]

NotSQL, that will work but it will give the wrong answer, i.e. it will give as many rows per currency as there are different rates for that currency

SELECT t.CurrencyCode
     , t.Date
     , t.Rate
  FROM ( SELECT CurrencyCode
              , MAX(Date) AS latest_date
           FROM Currencies
         GROUP
             BY CurrencyCode ) AS m
INNER
  JOIN Currencies AS t
    ON t.CurrencyCode = m.CurrencyCode
   AND t.Date = m.latest_date

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[NotSQL]

Thanks R937, most appreciated