aggregation.

</para>

<para>

While all aggregates below accept an optional

<literal>ORDER BY</literal> clause (as outlined in <xref

linkend="syntax-aggregates"/>), the clause has only been added to

aggregates whose output is affected by ordering.

</para>

<table id="functions-aggregate-table">

<title>General-Purpose Aggregate Functions</title>

<tgroup cols="2">

<indexterm>

<primary>array_agg</primary>

</indexterm>

<function>array_agg</function> ( <type>anynonarray</type> )

<function>array_agg</function> ( <type>anynonarray</type><literal>ORDER BY</literal> <literal>input_sort_columns</literal>)

<returnvalue>anyarray</returnvalue>

</para>

<para>

<row>

<entry role="func_table_entry"><para role="func_signature">

<function>array_agg</function> ( <type>anyarray</type> )

<function>array_agg</function> ( <type>anyarray</type><literal>ORDER BY</literal> <literal>input_sort_columns</literal>)

<returnvalue>anyarray</returnvalue>

</para>

<para>

<indexterm>

<primary>json_agg</primary>

</indexterm>

<function>json_agg</function> ( <type>anyelement</type> )

<function>json_agg</function> ( <type>anyelement</type><literal>ORDER BY</literal> <literal>input_sort_columns</literal>)

<returnvalue>json</returnvalue>

</para>

<para role="func_signature">

<indexterm>

<primary>jsonb_agg</primary>

</indexterm>

<function>jsonb_agg</function> ( <type>anyelement</type> )

<function>jsonb_agg</function> ( <type>anyelement</type><literal>ORDER BY</literal> <literal>input_sort_columns</literal>)

<returnvalue>jsonb</returnvalue>

</para>

<para>

</indexterm>

<function>json_object_agg</function> ( <parameter>key</parameter>

<type>"any"</type>, <parameter>value</parameter>

<type>"any"</type> )

<type>"any"</type>

<literal>ORDER BY</literal> <literal>input_sort_columns</literal> )

<returnvalue>json</returnvalue>

</para>

<para role="func_signature">

</indexterm>

<function>jsonb_object_agg</function> ( <parameter>key</parameter>

<type>"any"</type>, <parameter>value</parameter>

<type>"any"</type> )

<type>"any"</type>

<literal>ORDER BY</literal> <literal>input_sort_columns</literal> )

<returnvalue>jsonb</returnvalue>

</para>

<para>

</para>

<para role="func_signature">

<function>string_agg</function> ( <parameter>value</parameter>

<type>bytea</type>, <parameter>delimiter</parameter> <type>bytea</type> )

<type>bytea</type>, <parameter>delimiter</parameter> <type>bytea</type>

<literal>ORDER BY</literal> <literal>input_sort_columns</literal> )

<returnvalue>bytea</returnvalue>

</para>

<para>

<indexterm>

<primary>xmlagg</primary>

</indexterm>

<function>xmlagg</function> ( <type>xml</type> )

<function>xmlagg</function> ( <type>xml</type><literal>ORDER BY</literal> <literal>input_sort_columns</literal>)

<returnvalue>xml</returnvalue>

</para>

<para>

are always just expressions and cannot be output-column names or numbers.

For example:

<programlisting>

SELECT array_agg(a ORDER BY b DESC) FROM table;

WITH vals (v) AS ( VALUES (1),(3),(4),(3),(2) )

SELECT array_agg(v ORDER BY v DESC) FROM vals;

array_agg

-------------

{4,3,3,2,1}

</programlisting>

Since <type>jsonb</type> only keeps the last matching key, ordering

of its keys can be significant:

<programlisting>

WITH vals (k, v) AS ( VALUES ('key0','1'), ('key1','3'), ('key1','2') )

SELECT jsonb_object_agg(k, v ORDER BY v) FROM vals;

jsonb_object_agg

----------------------------

{"key0": "1", "key1": "3"}

</programlisting>

</para>

</para>

<para>

If <literal>DISTINCT</literal> is specified in addition to an

<replaceable>order_by_clause</replaceable>, then all the <literal>ORDER BY</literal>

expressions must match regular arguments of the aggregate; that is,

you cannot sort on an expression that is not included in the

<literal>DISTINCT</literal> list.

If <literal>DISTINCT</literal> is specified with an

<replaceable>order_by_clause</replaceable>, <literal>ORDER

BY</literal> expressions can only reference columns in the

<literal>DISTINCT</literal> list. For example:

<programlisting>

WITH vals (v) AS ( VALUES (1),(3),(4),(3),(2) )

SELECT array_agg(DISTINCT v ORDER BY v DESC) FROM vals;

array_agg

-----------

{4,3,2,1}

</programlisting>

</para>

<note>

<para>

The ability to specify both <literal>DISTINCT</literal> and <literal>ORDER BY</literal>

in an aggregate function is a <productname>PostgreSQL</productname> extension.

</para>

</note>

<para>

Placing <literal>ORDER BY</literal> within the aggregate's regular argument

list, as described so far, is used when ordering the input rows for