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