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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
