Re: Aggregate ORDER BY patch

On 2009-12-15 23:10 +0200, Tom Lane wrote:
> Andrew Gierth<andrew(at)tao11(dot)riddles(dot)org(dot)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 on
> select agg(x) from (select distinct on (x) x ... order by x,y)

FWIW, in my opinion the idea behind this patch is to not fall back on
hacks like that. This patch already goes beyond the standard and having
this seems like a useful feature in some cases. Although the DISTINCT
ON syntax would have a bit more resemblance on the existing syntax, I'd
still like to see agg(distinct x order by x,y).

Just my $0.02.

Regards,
Marko Tiikkaja