Re: Bug with ordering aggregates?

This is an area that the SQL standard didn't think through very clearly
(IMHO). They actually have two ways of specifying functions like this, one
is the ordered aggregate section that this syntax is modeled on, which is
indeed very confusing for multi-parameter aggregates. The other is the
hypothetical set function syntax which is actually much clearer for this
sort of operation, though I haven't dug deep enough into the standard to be
sure this wouldn't include any gotchas:

SELECT agg(parameter1, parameter2) WITHIN GROUP (ORDER BY column1 asc)

(See section 10.9 on <aggregate function> syntax)

Supporting the hypthothetical set functions could give a preferable syntax.

Regards,
Caleb

On 5/18/10 9:42 AM, "Stephen Frost" <sfrost(at)snowman(dot)net> wrote:

> * Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
>> Stephen Frost <sfrost(at)snowman(dot)net> writes:
>>> This doesn't seem right to me:
>>
>>> postgres=# select
>>> postgres-# string_agg(column1::text order by column1 asc,',')
>>> postgres-# from (values (3),(4),(1),(2)) a;
>>> string_agg
>>> ------------
>>> 1234
>>> (1 row)
>>
>> Looks fine to me: you have two ordering columns (the second rather
>> useless, but that's no matter).
>
> Ah, yeah, guess I'll just complain that having the order by look like
> it's an argument to an aggregate makes things confusing. Not much to be
> done about it though.
>
> Thanks,
>
> Stephen