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

"Jonah H. Harris" <jonah(dot)harris(at)gmail(dot)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