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