Re: aggregates, distinct, order by, and case - why won't this work

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> This...on 9.3
> SELECT array_agg(
> distinct case when v % 2 = 0 then 'odd' else 'even' end
> order by case when v % 2 = 0 then 1 else 2 end
> )
> FROM (VALUES (1), (2), (3)) val (v)

The error message seems pretty clear to me:

ERROR:  in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list

This is exactly the same as the complaint you'd get with a SELECT-level
DISTINCT, eg

regression=# create table ttt(a int, b int);
CREATE TABLE
regression=# select distinct a from ttt order by b;
ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 1: select distinct a from ttt order by b;
                                            ^

and the reason is the same too: the value of b is not necessarily unique
within any one group of rows with the same value of a, so it's not
well-defined what output order this is asking for.

In the example you give, it's possible for a human to see that the two
case expressions give values that must correlate perfectly.  But PG
doesn't try to do that kind of analysis.  It just insists that an ORDER
BY expression be one of the ones being DISTINCT'd on.

            regards, tom lane