SELECT query on Currency Rates

Question

Post reply

SELECT query on Currency Rates

ArnolddG

SSC-Addicted

Points: 464
More actions
June 20, 2015 at 8:27 am

#297992

Hi,
I have this query to select the highest currency rate from a currency table:
[font="Courier New"]SELECT
Target_currency AS [Currency]
,rate_exchange AS [Rate]
,date_L AS [Date]
FROM
rates
WHERE
target_currency IN ('USD','GBP','JPY')
ORDER BY
target_currency desc
,date_L desc[/font]
My output is something like this:
[font="Courier New"]CurrencyRateDate
USD 1,13572015-06-20 00:00:00.000
USD 1,37952014-03-31 00:00:00.000
USD 1,28352014-03-24 00:00:00.000
USD 1,28522013-04-05 00:00:00.000
JPY142,37552014-03-31 00:00:00.000
JPY1202014-03-24 00:00:00.000
JPY119,32013-04-05 00:00:00.000
GBP 0,82872014-03-31 00:00:00.000
GBP 0,08482014-03-24 00:00:00.000
GBP 0,084392013-04-05 00:00:00.000[/font]
How can I get the top 3 of the most recent rates from every rate in the WHERE filtering?
So:
[font="Courier New"]USD 1,13572015-06-20 00:00:00.000
JPY142,37552014-03-31 00:00:00.000
GBP 0,82872014-03-31 00:00:00.000[/font]
I have tried many solutions, but I can't get it to work.
Thanks for helping me...

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 1

This should be pretty straightforward with a CTE and ROW_NUMBER.

Something like this:

WITH OrderedByDate AS (

SELECT

RN=ROW_NUMBER() OVER (PARTITION BY target_currency ORDER BY date_l desc),

target_currency,

rate_exchange,

date_l

FROM rates)

SELECT target_currency as [Currency],

rate_exchange AS [Rate],

date_l as [Date]

FROM OrderedByDate

WHERE RN=1

AND target_currency IN ('USD','GBP','JPY')

ORDER BY

target_currency desc

,date_L desc

Cheers!

ArnolddG SSC-Addicted Points: 464 More actions · Answer 2

Thanks very much for your solution Jacob...

This works perfectly !

Unfortunately I haven't got a clue how this works.

Do you mind explaining it a bit? Just to understand what you did here?

Thanks again,

Arnold

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 3

I'm glad that helped, and I'll be happy to step through it as best I can!

So, the core of the solution is the ROW_NUMBER() function. It does exactly what you'd expect such a thing to do, and numbers the rows. The OVER clause for ROW_NUMBER () does two things.

First, the PARTITION BY tells it how to group the data. ROW_NUMBER() re-starts the numbering for each group.

Second, ORDER BY just tells it in what order to number the rows.

In this case, I told it to give each currency its own set of numbers (the 'PARTITION BY target_currency' bit), and to number the rows in descending order by date.

Together, that 'PARTITION BY target_currency ORDER BY date_l desc' clause meant that the row with the most recent date for each currency would have a ROW_NUMBER of 1. So, all we have to do now is return the rows where the output of ROW_NUMBER is 1.

However, you can't use windowing functions like ROW_NUMBER directly in a WHERE clause. To get around that, I defined a common table expression that just returned the output of ROW_NUMBER as a column (with the name RN), and then used WHERE RN=1 in the query against the CTE.

Hopefully that helps clear up how it works. If any of that was unclear, just let me know and I'll do my best to clarify!

Cheers!

ArnolddG SSC-Addicted Points: 464 More actions · Answer 4

It's very clear and good to understand for me.

So I now have a solution, AND the explanation of it.

Thanks again for your effort.

Arnold