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(at)Harte-Lyne(dot)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