CODEWITHSUNDEEP
sqloracle-database
Andrew
Andrew

Reputation: 29

Query showing exchange rates by date

Apologies if this is silly question...

I have a table that holds rates for multiple currencies at differing dates, as follows:

+-----------------------------+
| FOREKEY  FOREDATE  FORERATE |
+-----------------------------+
| 1        01/01/16  1.5      |
| 2        01/01/16  1.9      |
| 3        01/01/16  9.2      |
| 4        01/01/16  1.0      |
| 2        01/02/16  1.7      |
| 3        01/03/16  9.0      |
| 4        01/04/16  1.1      |
+-----------------------------+

I would like to create a query that gives the prevailing currency rate at any of the given dates.

I have tried the query below, but this does not show the FOREKEY 1 rate on 01/03/16, for example. 

SELECT
   F.FOREDATE,
   F.FOREKEY,
   F.FORERATE
FROM
  FORERATE F
       INNER JOIN 
        (SELECT
            MAX(FOREDATE) FOREDATE
        FROM 
            FORERATE 
         GROUP BY 
            FOREDATE) FSUB
        ON (F.FOREDATE  = FSUB.FOREDATE) OR (F.FOREDATE > FSUB.FOREDATE)

Any help gratefully received!

Upvotes: 1

Views: 122

Answers (1)

Wyatt Shipman
Wyatt Shipman

Reputation: 1789

You could use a SELECT MAX() in the where clause like below:

SELECT
   F.FOREDATE,
   F.FOREKEY,
   F.FORERATE
FROM FORERATE F
WHERE F.FOREDATE = (SELECT MAX(FOREDATE) 
                    FROM FORERATE FMD 
                    WHERE F.FOREKEY=FMD.FOREKEY)

Upvotes: 2

Related Questions