Re: Help with join syntax sought supplemental

On Wed, May 20, 2009 13:07, James B. Byrne wrote:
> This seems to be working.  I had to take a different approach as I
> had misapprehended GROUP BY completely.
>
>
> SELECT *
> FROM currency_exchange_rates AS xchg1
> WHERE id
>   IN (
>     SELECT id
>     FROM currency_exchange_rates as xchg2
>     WHERE
>          xchg1.currency_code_base = xchg2.currency_code_base
>          AND
>          xchg1.currency_code_quote = xchg2.currency_code_quote
>     ORDER BY currency_code_base,
>              currency_code_quote,
>              effective_from DESC
>     LIMIT 1 )
> ORDER BY currency_code_base,
>          currency_code_quote,
>          effective_from DESC


Looking at this I have to wonder what will be the effect of having
tens of thousands of rate-pairs on file.  Would this query be
improved by first doing a sub-query on base/quote pairs that
returned DISTINCT pairs and then do the IN condition using that?

--
***          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