James B. Byrne wrote:
> 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
Yeah, that's a good idea, now all you have to do is change the "limit 1"
to "limit 5" or whatever.
>
>
> 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?
>
Tough to say, you'd have to time it. As written the inside sub select
is going to be quick and return very few elements. I'd think this sql
would run fast.
-Andy