Re: Help with join syntax sought

James B. Byrne wrote:
> On Tue, May 19, 2009 17:43, Andy Colson wrote:
> .
>> What field is the source? currency_code_quote?
>>
>> -Andy
>>
>
> Here is the layout of the table:
>
> # Table name: currency_exchange_rates
> #
> # id :integer not null, primary key
> # currency_code_base :string(3) not null
> # currency_code_quote :string(3) not null
> # currency_exchange_rate :decimal(12, 6) not null
> # currency_exchange_source :string(255) not null
> # currency_exchange_type :string(4) not null
> # effective_from :datetime not null
> # superseded_after :datetime
> # created_at :datetime not null
> # created_by :string(255) default("unknown"),
> # not null
> # changed_at :datetime not null
> # changed_by :string(255) default("unknown"),
> # not null
> #
>
>
> I appreciate the help and I do not wish to burden you with too much
> detail. The situation is that currency pairs are assigned a nominal
> exchange rate by various markets based on actual trades. We read
> these rates from various sources and assign a type to that rate
> pair. We record the rate, the exact source of the rate and the
> datetime of its effectiveness.
>
> Rates, by their market driven nature, are always historic, so that
> it is not foreseen that an effective_from time-stamp will ever be in
> the future.
>
> 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.
>
> I am still feeling my way though SQL syntax and some of the results
> I am seeing simply do not make sense to me, at the moment. Some of
> my questions therefore may appear rather naive.
>
> The GROUP BY clause in particular is giving me trouble at the
> moment. I rather suspect that I have missed an important
> distinction with respect to GROUP BY and ORDER BY. GROUP BY I am
> now beginning to see is an accumulator, whereas I have been
> considering it more or less as a substitute for a report breakpoint.
>
>
> 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
>
> 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
>
>

For the first one, I think something like this would work, note this is
PG only, it wont work on other DB's:

... also guessing to the field names...

code_base, code_quote, effective_from, exchange_type, exchange_rate
CAD, AUD "2009-05-19 20:40:00" "CLSE" 1.131200

select distcint on (code_base, code_quote) code_base, code_quote,
effective_from, exchange_type, exchange_rate
from currency_exchange_rates
order by code_base, code_quote, effective_from [desc]

the [desc] is optional.. I always get ascending and descending w/dates
confused. One way you'll get the newest, the other you'll get the oldest.

This can be rewritten by joining a derived table, but this is a lot easer.

-Andy