"Kevin Grittner" <Kevin.Grittner@xxxxxxxxxxxx> writes: > Shaun Thomas <sthomas@xxxxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
