Question:
I have a table with three columns: the firstcontains the name of the currency, thesecond contains the currency rate and the third contains the actual date when thecurrency rate changed. They are in no particularorder, neither by currency, rate or date.My question is: how can I select a specific date and return the currency rate in effect for that date?

For example, the data might look like this:

    DEM   5.76   01/19/96    DKK   1.32   01/04/96    DEM   5.92   02/09/96    BEF   0.33   04/04/96    DEM   5.88   01/04/96    …   ….   ……..

How do I retrieve the actual currency rate forDeutche Mark, DEM, for January 20, 1996?(The answer should be 5.76.)

Answer:
Try this:

    SELECT value FROM Currencies        WHERE currency=’DEM’ AND        chg_date IN             (SELECT MAX(chg_date) FROM Currencies                WHERE chg_date <='01/20/96');