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