Re: Aggregate ORDER BY patch

2009/12/19 Marko Tiikkaja <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi>:
> 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).
>

when we are talking about extensions - did you thing about LIMIT clause?

select agg(distinct x order by x limit 10) ..

Regards
Pavel

> Just my $0.02.
>
>
> Regards,
> Marko Tiikkaja
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>