Help with join syntax sought
От | James B. Byrne |
---|---|
Тема | Help with join syntax sought |
Дата | |
Msg-id | 51424.216.185.71.24.1242756309.squirrel@webmail.harte-lyne.ca обсуждение исходный текст |
Ответы |
Re: Help with join syntax sought
(Andy Colson <andy@squeakycode.net>)
Re: Help with join syntax sought (Andy Colson <andy@squeakycode.net>) Re: Help with join syntax sought (Andy Colson <andy@squeakycode.net>) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления:
Предыдущее
От: Merlin MoncureДата:
Сообщение: Re: Providing an alternative result when there is no result