Re: Best way to get the latest revision from a table

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Shaun Thomas <sthomas@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