Re: ORDER BY and DISTINCT ON

Greg Stark <gsstark(at)mit(dot)edu> writes:
> So, like DISTINCT ON, GROUP BY also insists on the user providing the
> ORDER BY clause. I suppose you could argue postgres could implicitly
> introduce an extra sort step when the user-provided ORDER BY doesn't
> match the GROUP BY or DISTINCT ON clause but it seems like the user is
> probably confused if he really wants a random record and then sort on
> columns that weren't sorted previous to the DISTINCT ON.

This was discussed before --- see the archives. I believe the
conclusion was that the results would actually be nondeterministic
if we used two sort steps (that's what the code comment means by
"rather unpredictable"). This is not unrelated to the reasons why
people consider DISTINCT ON to be a messy feature ... ideally it
should be orthogonal to ORDER BY, but it simply isn't.

regards, tom lane