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

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> Shaun Thomas <sthomas(at)peak6(dot)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