"Kevin Grittner" <kevin.gritt...@wicourts.gov> writes:
> Shaun Thomas <stho...@peak6.com> wrote:
>> This actually looks like a perfect candidate for DISTINCT ON.
>>
>> SELECT DISTINCT ON (a, b) a, b, revision
>> FROM test
>> ORDER BY a, b DESC;
> I wouldn't say perfect. It runs about eight times slower than what
> I suggested and returns a fairly random value for revision instead
> of the max(revision).
Shaun's example is a bit off: normally, when using DISTINCT ON, you want
an ORDER BY key that uses all the given DISTINCT keys and then some
more. To get the max revision for each a/b combination it ought to be
SELECT DISTINCT ON (a, b) a, b, revision
FROM test
ORDER BY a, b, revision DESC;
As for speed, either one might be faster in a particular situation.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
