Thread: Help with join syntax sought
I have a requirement to select the effective exchange rate for a
number of currencies as of a specific date and time. The rates may
come from several sources for the same currency. For some
currencies the rate may be set infrequently. I have come close to
getting this to work but cannot seem to get the last bit figured
out. Thus my appeal for help.
The table currency_exchange_rates has a composite unique index made
up of:
fxr.currency_code_base
fxr.currency_code_quote
fxr.effective_from
fxr.currency_exchange_type
Here is what I have so far:
SELECT
fxr.currency_code_base AS fx_base,
fxr.currency_code_quote AS fx_quote,
fxr.effective_from AS fx_date,
fxr.currency_exchange_type AS fx_type,
fxr.currency_exchange_rate AS fx_rate
FROM
currency_exchange_rates AS fxr
LEFT OUTER JOIN
currency_exchange_rates AS fxr_j
ON
fxr.currency_code_base = fxr_j.currency_code_base
AND
fxr.currency_code_quote = fxr_j.currency_code_quote
AND
fxr.currency_exchange_type = fxr_j.currency_exchange_type
AND
fxr.effective_from >= fxr_j.effective_from
WHERE
fxr.currency_code_base = 'CAD'
AND
fxr.effective_from <= current_timestamp
GROUP BY
fx_base,
fxr.currency_code_quote,
fx_date,
fxr.currency_exchange_type,
fx_rate
HAVING
COUNT(fxr.currency_code_quote) = 1
ORDER BY
fx_base,
fxr.currency_code_quote,
fx_date DESC
My problem with this version is that currencies with rates from more
than one type show up for each type. This I do not want. I want
only the most recent regardless of type. However, I need to display
the type in the final report.
Further, if I take the fxr.currency_exchange_type out of the SELECT
columns, making the appropriate adjustments elsewhere, then all
those currencies with more than one type are not selected at all.
I am perplexed why I cannot select a column from the table without
having to include it in the GROUP BY clause as well.
Any help is welcomed.
--
*** 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
James B. Byrne wrote: > I am perplexed why I cannot select a column from the table without > having to include it in the GROUP BY clause as well. > > Any help is welcomed. > Group by is saying "I want only one row returned for each distinct value in this column" so a food table like this: name | type -------------- apple | fruit pie | desert orange| fruit if you: select name, type from food group by type your saying, give me only one row for each "type"... but there are two records where type = 'fruit', so how do you return two values (apple, orange) in only one row? That's why all fields in the select list must be an aggregate function, or in the group by list. so: select max(name), type from food group by type works cuz we only get one name (the max name) back for each type. or: select name, type from food group by type, name which in our example is kinda pointless, but still, give us the distinct items for "type, name". -Andy
James B. Byrne wrote: > I have a requirement to select the effective exchange rate for a > number of currencies as of a specific date and time. The rates may > come from several sources for the same currency. For some > currencies the rate may be set infrequently. I have come close to > getting this to work but cannot seem to get the last bit figured > out. Thus my appeal for help. > > The table currency_exchange_rates has a composite unique index made > up of: > > fxr.currency_code_base > fxr.currency_code_quote > fxr.effective_from > fxr.currency_exchange_type > > Here is what I have so far: > > SELECT > fxr.currency_code_base AS fx_base, > fxr.currency_code_quote AS fx_quote, > fxr.effective_from AS fx_date, > fxr.currency_exchange_type AS fx_type, > fxr.currency_exchange_rate AS fx_rate > > FROM > currency_exchange_rates AS fxr > > LEFT OUTER JOIN > currency_exchange_rates AS fxr_j > > ON > fxr.currency_code_base = fxr_j.currency_code_base > AND > fxr.currency_code_quote = fxr_j.currency_code_quote > AND > fxr.currency_exchange_type = fxr_j.currency_exchange_type > AND > fxr.effective_from >= fxr_j.effective_from > > WHERE > fxr.currency_code_base = 'CAD' > AND > fxr.effective_from <= current_timestamp > > GROUP BY > fx_base, > fxr.currency_code_quote, > fx_date, > fxr.currency_exchange_type, > fx_rate > > HAVING > COUNT(fxr.currency_code_quote) = 1 > > ORDER BY > fx_base, > fxr.currency_code_quote, > fx_date DESC > > > My problem with this version is that currencies with rates from more > than one type show up for each type. This I do not want. I want > only the most recent regardless of type. However, I need to display > the type in the final report. > > Further, if I take the fxr.currency_exchange_type out of the SELECT > columns, making the appropriate adjustments elsewhere, then all > those currencies with more than one type are not selected at all. > > I am perplexed why I cannot select a column from the table without > having to include it in the GROUP BY clause as well. > > Any help is welcomed. > If your query above is getting you mostly what you want, just use it as a derived table. so: select subtable.fx_base, etc from ( > SELECT > fxr.currency_code_base AS fx_base, > fxr.currency_code_quote AS fx_quote, > fxr.effective_from AS fx_date, > fxr.currency_exchange_type AS fx_type, > fxr.currency_exchange_rate AS fx_rate > > FROM > currency_exchange_rates AS fxr > > LEFT OUTER JOIN > currency_exchange_rates AS fxr_j > > ON > fxr.currency_code_base = fxr_j.currency_code_base > AND > fxr.currency_code_quote = fxr_j.currency_code_quote > AND > fxr.currency_exchange_type = fxr_j.currency_exchange_type > AND > fxr.effective_from >= fxr_j.effective_from > > WHERE > fxr.currency_code_base = 'CAD' > AND > fxr.effective_from <= current_timestamp > > GROUP BY > fx_base, > fxr.currency_code_quote, > fx_date, > fxr.currency_exchange_type, > fx_rate > > HAVING > COUNT(fxr.currency_code_quote) = 1 > > ORDER BY > fx_base, > fxr.currency_code_quote, > fx_date DESC ) as subtable
On Tue, May 19, 2009 16:41, Andy Colson wrote: > If your query above is getting you mostly what you want, just use it > as a derived table. > I lack the experience to understand what this means. If, as you suggest, I use a subquery as the expression to the main SELECT and for it I use the syntax that returns every distinct combination of base, quote, timestamp, and type, then what? Unless I am missing something then I still have too many rows for those currencies with more than one type. -- *** 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
On Tue, May 19, 2009 17:02, Andy Colson wrote: > > so: select max(name), type from food group by type > works cuz we only get one name (the max name) back for each type. > > or: select name, type from food group by type, name > which in our example is kinda pointless, but still, give us the > distinct > items for "type, name". Thanks. I think I am beginning to see this. So, if there are more than one type for a given currency code and I do not select and group by type then the having count(whatever) = 1 means that these rows are not selected either. Is that correct? -- *** 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
James B. Byrne wrote: > On Tue, May 19, 2009 16:41, Andy Colson wrote: > >> If your query above is getting you mostly what you want, just use it >> as a derived table. >> > > I lack the experience to understand what this means. > > If, as you suggest, I use a subquery as the expression to the main > SELECT and for it I use the syntax that returns every distinct > combination of base, quote, timestamp, and type, then what? > > Unless I am missing something then I still have too many rows for > those currencies with more than one type. > I was not offering a specific answer, just another tool you could use to massage the data more. What I'm saying is, with that first query you got the data into a new format.... Think of that result set as a table itself. As if you had done: create table tmp (lots of field); insert into tmp select (that big query you first posted) now can you select from tmp to get your answer? Once again, I may be leading you down the wrong path, and as I said before I don't know your data/layouts, and I didn't study your sql very much. -Andy
James B. Byrne wrote:
> On Tue, May 19, 2009 17:02, Andy Colson wrote:
>
>> so: select max(name), type from food group by type
>> works cuz we only get one name (the max name) back for each type.
>>
>> or: select name, type from food group by type, name
>> which in our example is kinda pointless, but still, give us the
>> distinct
>> items for "type, name".
>
> Thanks. I think I am beginning to see this. So, if there are more
> than one type for a given currency code and I do not select and
> group by type then the having count(whatever) = 1 means that these
> rows are not selected either. Is that correct?
>
>
I'm not familiar with your data, and I didn't study your sql very hard.
I'm not sure what this will do:
HAVING
COUNT(fxr.currency_code_quote) = 1
The only time I have ever used HAVING is like:
select name from something group by name having count(*) > 1
to find duplicate name's.
you're group by is on 5 fields, but the count is only on one.... so...
If a count is in the select part (like select count(name) from stuff)
it only counts when name is not null... so maybe that having is saying
count where currency_code_quote is not null and there is only one record
per group... I dunno.
-Andy
James B. Byrne wrote: > I have a requirement to select the effective exchange rate for a > number of currencies as of a specific date and time. The rates may > come from several sources for the same currency. For some > currencies the rate may be set infrequently. I have come close to > getting this to work but cannot seem to get the last bit figured > out. Thus my appeal for help. > > > Here is what I have so far: > > SELECT > fxr.currency_code_base AS fx_base, > fxr.currency_code_quote AS fx_quote, > fxr.effective_from AS fx_date, > fxr.currency_exchange_type AS fx_type, > fxr.currency_exchange_rate AS fx_rate > > FROM > currency_exchange_rates AS fxr > > LEFT OUTER JOIN > currency_exchange_rates AS fxr_j > > ON > fxr.currency_code_base = fxr_j.currency_code_base > AND > fxr.currency_code_quote = fxr_j.currency_code_quote > AND > fxr.currency_exchange_type = fxr_j.currency_exchange_type > AND > fxr.effective_from >= fxr_j.effective_from > > WHERE > fxr.currency_code_base = 'CAD' > AND > fxr.effective_from <= current_timestamp > > GROUP BY > fx_base, > fxr.currency_code_quote, > fx_date, > fxr.currency_exchange_type, > fx_rate > > HAVING > COUNT(fxr.currency_code_quote) = 1 > > ORDER BY > fx_base, > fxr.currency_code_quote, > fx_date DESC > > I see currency_code_base = 'CAD', so you are looking for the most recent Canadian exchange rate. > The rates may > come from several sources for the same currency. What field is the source? currency_code_quote? -Andy
Andy Colson wrote: > James B. Byrne wrote: >> I have a requirement to select the effective exchange rate for a >> number of currencies as of a specific date and time. The rates may >> come from several sources for the same currency. For some >> currencies the rate may be set infrequently. I have come close to >> getting this to work but cannot seem to get the last bit figured >> out. Thus my appeal for help. >> >> >> Here is what I have so far: >> >> SELECT >> fxr.currency_code_base AS fx_base, >> fxr.currency_code_quote AS fx_quote, >> fxr.effective_from AS fx_date, >> fxr.currency_exchange_type AS fx_type, >> fxr.currency_exchange_rate AS fx_rate >> >> FROM >> currency_exchange_rates AS fxr >> >> LEFT OUTER JOIN >> currency_exchange_rates AS fxr_j >> >> ON >> fxr.currency_code_base = fxr_j.currency_code_base >> AND >> fxr.currency_code_quote = fxr_j.currency_code_quote >> AND >> fxr.currency_exchange_type = fxr_j.currency_exchange_type >> AND >> fxr.effective_from >= fxr_j.effective_from >> >> WHERE >> fxr.currency_code_base = 'CAD' >> AND >> fxr.effective_from <= current_timestamp >> >> GROUP BY >> fx_base, >> fxr.currency_code_quote, >> fx_date, >> fxr.currency_exchange_type, >> fx_rate >> >> HAVING >> COUNT(fxr.currency_code_quote) = 1 >> >> ORDER BY >> fx_base, >> fxr.currency_code_quote, >> fx_date DESC >> >> > > I see currency_code_base = 'CAD', so you are looking for the most recent > Canadian exchange rate. > > > > The rates may > > come from several sources for the same currency. > > What field is the source? currency_code_quote? > > -Andy Hum... I can quite figure what we are looking for. you say: currencies as of a specific date and time but there is not date stuff in the where... so we'll ignore that. you say: come from several sources but doesnt seem important, we'll ignore that. you say: I want only the most recent regardless of type so type is unimporttant How about this: select * from fxr where code_base = 'CAD' and effective_from = (select max(effective_from) from fxr f2 where f2.code_base=fxr.code_base and f2.code_quote=fxr.code_quote); (forgive the shortened names), it selects any 'CAD' of only the most recent time, based on code_base and code_quote. (had to guess at the fields) Oh! I see one problem.. if the effective_from is exactly the same it could return more records than you want. Have to think about that... -Andy
On May 19, 2009, at 11:29 PM, Andy Colson wrote: > I'm not sure what this will do: > > HAVING > COUNT(fxr.currency_code_quote) = 1 > > The only time I have ever used HAVING is like: > > select name from something group by name having count(*) > 1 > > to find duplicate name's. That will leave out all results of multiple rows from the group by, which is not the desired result I think. IIUC the desired result is to leave out duplicates after the first match, not to leave out all results that have duplicates. I think you want something like: HAVING effective_from = MAX(effective_from) Or you ORDER BY effective_from DESC and use DISTINCT ON to ignore the duplicates after the first match (which is the newest currency due to the ordering). I wonder whether it's possible to have effective_from dates in the future though, that would complicate things slightly more... Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4a133d4d10091830814072!
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
This seems to be working. I had to take a different approach as I
had misapprehended GROUP BY completely.
SELECT *
FROM currency_exchange_rates AS xchg1
WHERE id
IN (
SELECT id
FROM currency_exchange_rates as xchg2
WHERE
xchg1.currency_code_base = xchg2.currency_code_base
AND
xchg1.currency_code_quote = xchg2.currency_code_quote
ORDER BY currency_code_base,
currency_code_quote,
effective_from DESC
LIMIT 1 )
ORDER BY currency_code_base,
currency_code_quote,
effective_from DESC
--
*** 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
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
On Wed, May 20, 2009 13:07, James B. Byrne wrote: > This seems to be working. I had to take a different approach as I > had misapprehended GROUP BY completely. > > > SELECT * > FROM currency_exchange_rates AS xchg1 > WHERE id > IN ( > SELECT id > FROM currency_exchange_rates as xchg2 > WHERE > xchg1.currency_code_base = xchg2.currency_code_base > AND > xchg1.currency_code_quote = xchg2.currency_code_quote > ORDER BY currency_code_base, > currency_code_quote, > effective_from DESC > LIMIT 1 ) > ORDER BY currency_code_base, > currency_code_quote, > effective_from DESC Looking at this I have to wonder what will be the effect of having tens of thousands of rate-pairs on file. Would this query be improved by first doing a sub-query on base/quote pairs that returned DISTINCT pairs and then do the IN condition using that? -- *** 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
James B. Byrne wrote: > On Wed, May 20, 2009 13:07, James B. Byrne wrote: >> This seems to be working. I had to take a different approach as I >> had misapprehended GROUP BY completely. >> >> >> SELECT * >> FROM currency_exchange_rates AS xchg1 >> WHERE id >> IN ( >> SELECT id >> FROM currency_exchange_rates as xchg2 >> WHERE >> xchg1.currency_code_base = xchg2.currency_code_base >> AND >> xchg1.currency_code_quote = xchg2.currency_code_quote >> ORDER BY currency_code_base, >> currency_code_quote, >> effective_from DESC >> LIMIT 1 ) >> ORDER BY currency_code_base, >> currency_code_quote, >> effective_from DESC Yeah, that's a good idea, now all you have to do is change the "limit 1" to "limit 5" or whatever. > > > Looking at this I have to wonder what will be the effect of having > tens of thousands of rate-pairs on file. Would this query be > improved by first doing a sub-query on base/quote pairs that > returned DISTINCT pairs and then do the IN condition using that? > Tough to say, you'd have to time it. As written the inside sub select is going to be quick and return very few elements. I'd think this sql would run fast. -Andy
In article <43639.216.185.71.24.1242834374.squirrel@webmail.harte-lyne.ca>,
"James B. Byrne" <byrnejb@Harte-Lyne.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.
On May 20, 2009, at 7:17 PM, James B. Byrne wrote: > Looking at this I have to wonder what will be the effect of having > tens of thousands of rate-pairs on file. Would this query be > improved by first doing a sub-query on base/quote pairs that > returned DISTINCT pairs and then do the IN condition using that? If it turns out to be a problem a way around is to keep a reference to the 'actual' conversion rates from another table. Which ones are 'actual' would be updated by an INSERT trigger on your rates table. The amount of data in the new table (and subsequently the index on it's PK) would be far smaller and therefore likely a lot faster to query. I've done something similar in a database where a history of states about records was kept around. Determining the 'actual' state was relatively slow because it was difficult to determine a method to uniquely point to it (the same issue with determining the latest timestamp of a group of records for the same data). Adding an FK from the record to it's 'actual' status record improved things a lot and had the added benefit that other derived information (detailed user information) was still easy to obtain with a simple join. But as people often say here, premature optimisation is a waste of time, so don't go that route unless you have a reason to expect problems in that area. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4a15269c10092027810544!
On Thu, May 21, 2009 06:02, Alban Hertroys wrote: > > But as people often say here, premature optimisation is a waste of > time, so don't go that route unless you have a reason to expect > problems in that area. > That was my very thought when I sent that message. On the other hand, in case I was doing something out of ignorance that was notoriously wrong, it seemed best to ask. Regards, -- *** 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