Postgres Pro
Downloads
Home   >   mailing lists

Re: ARRAY_AGG(DISTINCT a ORDER BY b) Error - Mailing list pgsql-general

From Tom Lane
Subject Re: ARRAY_AGG(DISTINCT a ORDER BY b) Error
Date
Msg-id 6119.1314832223@sss.pgh.pa.us
Whole thread Raw
In response to ARRAY_AGG(DISTINCT a ORDER BY b) Error  ("David Johnston" <polobo@yahoo.com>)
Responses Re: ARRAY_AGG(DISTINCT a ORDER BY b) Error  ("David Johnston" <polobo@yahoo.com>)
List pgsql-general
Tree view 
"David Johnston" <polobo@yahoo.com> writes:
> "in an aggregate with DISTINCT, ORDER BY expressions must appear in argument
> list"

> Why?

Because the results are ill-defined otherwise.  In your example,

>    ... ARRAY_AGG(DISTINCT accountnumber ORDER BY amount DESC) ...

there may be many rows with the same "accountnumber" and yet different
"amount" values.  Which of those amounts should be used as the sort key
for the aggregated row?

            regards, tom lane

pgsql-general by date:

Previous
From: "David Johnston"
Date:
Subject: ARRAY_AGG(DISTINCT a ORDER BY b) Error
Next
From: Thom Brown
Date:
Subject: Re: ARRAY_AGG(DISTINCT a ORDER BY b) Error