Re: ordered aggregates using WITHIN GROUP (was Re: can somebody execute this query on Oracle 11.2g and send result?)

"Jonah H. Harris" <jonah.harris@gmail.com> writes:
>> http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/functions087.htm
>> 
>> Defines:
>> 
>> *LISTAGG* (measure_expr [, 'delimiter_expr'])
>> *WITHIN GROUP* (order_by_clause) [*OVER* query_partition_clause]

Hmph.  I don't know what would possess them to model their function on
the rank-function syntax extension rather than ARRAY_AGG.  The latter
seems a lot closer to the functionality that's actually needed.  I'm
still trying to wrap my brain around what the spec says about the
rank-function syntax, but it's notable that the order-by clause is
tightly tied to the aggregate input value(s) --- the sort expressions
have to have the same number and types as the inputs.  Which is
certainly not very sensible for listagg.

Can anyone figure out exactly what SQL:2008 10.9 rule 6 is actually saying?
The references to VE1..VEk in the scalar subquery seem to me to be
semantically invalid.  They would be sensible if this were a window
function, but it's an aggregate, so I don't understand what row they'd
be evaluated with respect to.
        regards, tom lane