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

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


--
***          E-Mail is NOT a SECURE channel          ***
James B. Byrne                mailto:ByrneJB@Harte-Lyne.ca
Harte & Lyne Limited          http://www.harte-lyne.ca
9 Brockley Drive              vox: +1 905 561 1241
Hamilton, Ontario             fax: +1 905 561 0757
Canada  L8E 3C3