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