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.
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? |