ARRAY_AGG(DISTINCT a ORDER BY b) Error
| От | David Johnston |
|---|---|
| Тема | ARRAY_AGG(DISTINCT a ORDER BY b) Error |
| Дата | |
| Msg-id | 02af01cc6830$f671e500$e355af00$@yahoo.com обсуждение исходный текст |
| Ответы |
Re: ARRAY_AGG(DISTINCT a ORDER BY b) Error
Re: ARRAY_AGG(DISTINCT a ORDER BY b) Error |
| Список | 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.
В списке pgsql-general по дате отправления: