Re: Help with join syntax sought - Mailing list pgsql-general

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