Re: Help with join syntax sought

In article <43639(dot)216(dot)185(dot)71(dot)24(dot)1242834374(dot)squirrel(at)webmail(dot)harte-lyne(dot)ca>,
"James B. Byrne" <byrnejb(at)Harte-Lyne(dot)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.