Andrew Gierth <andrew@tao11.riddles.org.uk> writes:
> Notice that there are cases where agg(distinct x order by x) is
> nondeterministic while agg(distinct x order by x,y) is deterministic.
Well, I think what you're really describing is a case where you're using
the wrong sort opclass. If the aggregate can distinguish two values of
x, and the sort operator can't, use another sort operator that can.
If we really wanted to take the above seriously, my opinion is that
we ought to introduce DISTINCT ON in aggregates. However, at that
point you lose the argument of standard syntax, so it's not real
clear why you shouldn't just fall back onselect agg(x) from (select distinct on (x) x ... order by x,y)
regards, tom lane