Re: Help with join syntax sought - Mailing list pgsql-general

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