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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
