ARRAY_AGG(DISTINCT a ORDER BY b) Error

From: "David Johnston" <polobo(at)yahoo(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: ARRAY_AGG(DISTINCT a ORDER BY b) Error
Date: 2011-08-31 22:54:37
Message-ID: 02af01cc6830$f671e500$e355af00$@yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

"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'})

David J.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2011-08-31 23:10:23 Re: ARRAY_AGG(DISTINCT a ORDER BY b) Error
Previous Message Ben Chobot 2011-08-31 22:49:18 Re: regression between 8.4.8 and 8.4.2?