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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Jonah H(dot) Harris" <jonah(dot)harris(at)gmail(dot)com>
Cc: Alvaro Herrera <alvherre(at)commandprompt(dot)com>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: ordered aggregates using WITHIN GROUP (was Re: can somebody execute this query on Oracle 11.2g and send result?)
Date: 2010-01-29 17:55:05
Message-ID: 6548.1264787705@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

"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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Simon Riggs 2010-01-29 18:08:37 Re: Hot Standby: Relation-specific deferred conflict resolution
Previous Message Joshua D. Drake 2010-01-29 17:31:26 Re: Hot Standby: Relation-specific deferred conflict resolution