Re: Help with join syntax sought

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