Re: ARRAY_AGG(DISTINCT a ORDER BY b) Error

On 31 August 2011 23:54, David Johnston <polobo(at)yahoo(dot)com> wrote:
> "in an aggregate with DISTINCT, ORDER BY expressions must appear in argument
> list"
>
> Why?
>
> If I add the fields of the ORDER BY expression to the DISTINCT clause I can
> no longer use DISTINCT since the ORDER BY values are not unique. Nor do I
> want the contents of the final ARRAY to contain the ORDER BY column.
>
> I presuppose this is a technical limitation since my particular use-case
> (and I've come across a few cases where this would be useful) doesn't seem
> that obscure.
>
> My specific sample query (use-case) is as follows:
>
> SELECT control, ARRAY_AGG(DISTINCT accountnumber ORDER BY amount DESC)
> FROM (VALUES ('A', '1000', 100), ('A', '1000', -50), ('A', '2000',200))
> accountdetail (control, accountnumber, amount)
> GROUP BY control
>
> I want to create an array of the unique account numbers associated with a
> control with the ordering of the array matching the order of the amounts.
> In this case I would want the output to be:
>
> (A, {'2000','1000'})

I'm not sure that makes sense. If you're aggregating accountnumber as
an array of distinct values, what do you expect your query to output
if, say you had the following:

accountnumber, amount
1000,100
2000,200
1000,300

You've ordered by amount, but accountnumber has 2 identical values,
where the amount is less than the amount corresponding to
accountnumber 2000 in one instance, but greater in another. Where
does 1000 appear? Before or after 2000?

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company