In article <43639.216.185.71.24.1242834374.squirrel@webmail.harte-lyne.ca>,
"James B. Byrne" <byrnejb@Harte-Lyne.ca> writes:
> What I want to be able to do is to return the most recent rate for
> all unique rate-pairs, irrespective of type. I also have the
> requirement to return the 5 most recent rates for each rate-pair,
> thus the HAVING count(*) = 1, which I thought would allow me to
> simply change the test to HAVING count(*) <= 5 in that case.
The following queries seem to return what you want.
> Given this:
> CAD AUD "2009-05-19 16:15:00" "NOON" 1.146300
> CAD AUD "2009-05-19 20:40:00" "CLSE" 1.131200
> CAD AUD "2009-05-17 16:15:00" "NOON" 1.151300
> CAD AUD "2009-05-17 20:40:00" "CLSE" 1.141100
> CAD AUD "2009-05-16 16:15:00" "NOON" 1.143700
> CAD AUD "2009-05-16 20:40:00" "CLSE" 1.142100
> CAD USD "2009-05-19 16:15:00" "NOON" 0.864400
> CAD USD "2009-05-19 20:40:00" "CLSE" 0.843100
> What I want to see in the final output is
> CAD AUD "2009-05-19 20:40:00" "CLSE" 1.131200
> CAD USD "2009-05-19 20:40:00" "CLSE" 0.843100
SELECT t1.currency_code_base AS c1, t1.currency_code_quote AS c2,
t1.effective_from AS dt, t1.currency_exchange_type AS type,
t1.currency_exchange_rate AS rate
FROM currency_exchange_rates t1
WHERE t1.effective_from = (
SELECT max(t2.effective_from)
FROM currency_exchange_rates t2
WHERE t2.currency_code_base = t1.currency_code_base
AND t2.currency_code_quote = t1.currency_code_quote
)
> and if requested for the 5 most recent then I want to see this:
> CAD AUD "2009-05-19 20:40:00" "CLSE" 1.131200
> CAD AUD "2009-05-19 16:15:00" "NOON" 1.146300
> CAD AUD "2009-05-17 20:40:00" "CLSE" 1.141100
> CAD AUD "2009-05-17 16:15:00" "NOON" 1.151300
> CAD AUD "2009-05-16 20:40:00" "CLSE" 1.142100
> CAD USD "2009-05-19 20:40:00" "CLSE" 0.843100
> CAD USD "2009-05-19 16:15:00" "NOON" 0.864400
SELECT t1.currency_code_base AS c1, t1.currency_code_quote AS c2,
t1.effective_from AS dt, t1.currency_exchange_type AS type,
t1.currency_exchange_rate AS rate
FROM currency_exchange_rates t1
WHERE (
SELECT count(*)
FROM currency_exchange_rates t2
WHERE t2.currency_code_base = t1.currency_code_base
AND t2.currency_code_quote = t1.currency_code_quote
AND t2.effective_from > t1.effective_from
) <= 5
ORDER BY t1.currency_code_base, t1.currency_code_quote,
t1.effective_from DESC
Both of them must touch all currency_code_base/currency_code_quote pairs;
maybe you can avoid that with a composite index.
