Re: DISTINCT ... ORDER BY

On Wed, 5 Nov 2003, Nabil Sayegh wrote:

> Stephan Szabo wrote:
>
> > Well, what did you get when you tried something like Bruno's updated
>
> It worked.
> But I thought you were trying to tell me that it could be non-deterministic.

DISTINCT ON (blah) is different from DISTINCT and is a PostgreSQL
extension.

IIRC, it'll take the first row that matches the distincted on columns
based on the ordering rules from the order by. So, that's the part that
determines the "which of the matching places in the sort order" you want
to use (whichever is first in the ordering) which is something that
DISTINCT doesn't provide.

The difference here is between the question:
"Get the distinct values of a column ordered descendingly by the following
boolean expressions: expr1, expr2, ..."

And
"Get the distinct values of a column ordered descendingly by the following
boolean expressions: expr1, expr2, ... for the row in each group of rows
having a particular distinct value of the column having the highest
value of expr1, and in the case of ties, the highest value of expr2, ..."

The difference is small, but very important.

> > example or my group by one, and lets work from there.
>
> I tried the group by method but as my order by expressions are booleans I couldn't use min()
> and didn't find an applicable aggregate function.

You'd have to build a min(boolean) (which I'm sortof surprised isn't
there) or use a case to convert it into an integer.  Or given that it
looks like you were doing DESC sorts, you'd probably want max().
DISTINCT ON is a better choice for postgresql, it'll almost certainly be
faster, but it's not very standard.