Skip to content

Commit 7751352

Browse files
bmomjianbmomjian
committed
doc: mention ORDER BY for some aggregates, add ORDER BY examples
Discussion: https://postgr.es/m/CAKFQuwb+4SWnfrfQKB-UM1P1x97Xk+ybSar4xM32XGLd=fq9bA@mail.gmail.com Co-authored-by: David G. Johnston Backpatch-through: master
1 parent 95a610b commit 7751352

File tree

2 files changed

+43
-21
lines changed

2 files changed

+43
-21
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 18 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -20288,6 +20288,13 @@ SELECT NULLIF(value, '(none)') ...
2028820288
aggregation.
2028920289
</para>
2029020290

20291+
<para>
20292+
While all aggregates below accept an optional
20293+
<literal>ORDER BY</literal> clause (as outlined in <xref
20294+
linkend="syntax-aggregates"/>), the clause has only been added to
20295+
aggregates whose output is affected by ordering.
20296+
</para>
20297+
2029120298
<table id="functions-aggregate-table">
2029220299
<title>General-Purpose Aggregate Functions</title>
2029320300
<tgroup cols="2">
@@ -20325,7 +20332,7 @@ SELECT NULLIF(value, '(none)') ...
2032520332
<indexterm>
2032620333
<primary>array_agg</primary>
2032720334
</indexterm>
20328-
<function>array_agg</function> ( <type>anynonarray</type> )
20335+
<function>array_agg</function> ( <type>anynonarray</type> <literal>ORDER BY</literal> <literal>input_sort_columns</literal> )
2032920336
<returnvalue>anyarray</returnvalue>
2033020337
</para>
2033120338
<para>
@@ -20336,7 +20343,7 @@ SELECT NULLIF(value, '(none)') ...
2033620343

2033720344
<row>
2033820345
<entry role="func_table_entry"><para role="func_signature">
20339-
<function>array_agg</function> ( <type>anyarray</type> )
20346+
<function>array_agg</function> ( <type>anyarray</type> <literal>ORDER BY</literal> <literal>input_sort_columns</literal> )
2034020347
<returnvalue>anyarray</returnvalue>
2034120348
</para>
2034220349
<para>
@@ -20541,14 +20548,14 @@ SELECT NULLIF(value, '(none)') ...
2054120548
<indexterm>
2054220549
<primary>json_agg</primary>
2054320550
</indexterm>
20544-
<function>json_agg</function> ( <type>anyelement</type> )
20551+
<function>json_agg</function> ( <type>anyelement</type> <literal>ORDER BY</literal> <literal>input_sort_columns</literal> )
2054520552
<returnvalue>json</returnvalue>
2054620553
</para>
2054720554
<para role="func_signature">
2054820555
<indexterm>
2054920556
<primary>jsonb_agg</primary>
2055020557
</indexterm>
20551-
<function>jsonb_agg</function> ( <type>anyelement</type> )
20558+
<function>jsonb_agg</function> ( <type>anyelement</type> <literal>ORDER BY</literal> <literal>input_sort_columns</literal> )
2055220559
<returnvalue>jsonb</returnvalue>
2055320560
</para>
2055420561
<para>
@@ -20588,7 +20595,8 @@ SELECT NULLIF(value, '(none)') ...
2058820595
</indexterm>
2058920596
<function>json_object_agg</function> ( <parameter>key</parameter>
2059020597
<type>"any"</type>, <parameter>value</parameter>
20591-
<type>"any"</type> )
20598+
<type>"any"</type>
20599+
<literal>ORDER BY</literal> <literal>input_sort_columns</literal> )
2059220600
<returnvalue>json</returnvalue>
2059320601
</para>
2059420602
<para role="func_signature">
@@ -20597,7 +20605,8 @@ SELECT NULLIF(value, '(none)') ...
2059720605
</indexterm>
2059820606
<function>jsonb_object_agg</function> ( <parameter>key</parameter>
2059920607
<type>"any"</type>, <parameter>value</parameter>
20600-
<type>"any"</type> )
20608+
<type>"any"</type>
20609+
<literal>ORDER BY</literal> <literal>input_sort_columns</literal> )
2060120610
<returnvalue>jsonb</returnvalue>
2060220611
</para>
2060320612
<para>
@@ -20834,7 +20843,8 @@ SELECT NULLIF(value, '(none)') ...
2083420843
</para>
2083520844
<para role="func_signature">
2083620845
<function>string_agg</function> ( <parameter>value</parameter>
20837-
<type>bytea</type>, <parameter>delimiter</parameter> <type>bytea</type> )
20846+
<type>bytea</type>, <parameter>delimiter</parameter> <type>bytea</type>
20847+
<literal>ORDER BY</literal> <literal>input_sort_columns</literal> )
2083820848
<returnvalue>bytea</returnvalue>
2083920849
</para>
2084020850
<para>
@@ -20892,7 +20902,7 @@ SELECT NULLIF(value, '(none)') ...
2089220902
<indexterm>
2089320903
<primary>xmlagg</primary>
2089420904
</indexterm>
20895-
<function>xmlagg</function> ( <type>xml</type> )
20905+
<function>xmlagg</function> ( <type>xml</type> <literal>ORDER BY</literal> <literal>input_sort_columns</literal> )
2089620906
<returnvalue>xml</returnvalue>
2089720907
</para>
2089820908
<para>

‎doc/src/sgml/syntax.sgml

Lines changed: 25 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -1647,7 +1647,20 @@ sqrt(2)
16471647
are always just expressions and cannot be output-column names or numbers.
16481648
For example:
16491649
<programlisting>
1650-
SELECT array_agg(a ORDER BY b DESC) FROM table;
1650+
WITH vals (v) AS ( VALUES (1),(3),(4),(3),(2) )
1651+
SELECT array_agg(v ORDER BY v DESC) FROM vals;
1652+
array_agg
1653+
-------------
1654+
{4,3,3,2,1}
1655+
</programlisting>
1656+
Since <type>jsonb</type> only keeps the last matching key, ordering
1657+
of its keys can be significant:
1658+
<programlisting>
1659+
WITH vals (k, v) AS ( VALUES ('key0','1'), ('key1','3'), ('key1','2') )
1660+
SELECT jsonb_object_agg(k, v ORDER BY v) FROM vals;
1661+
jsonb_object_agg
1662+
----------------------------
1663+
{"key0": "1", "key1": "3"}
16511664
</programlisting>
16521665
</para>
16531666

@@ -1668,20 +1681,19 @@ SELECT string_agg(a ORDER BY a, ',') FROM table; -- incorrect
16681681
</para>
16691682

16701683
<para>
1671-
If <literal>DISTINCT</literal> is specified in addition to an
1672-
<replaceable>order_by_clause</replaceable>, then all the <literal>ORDER BY</literal>
1673-
expressions must match regular arguments of the aggregate; that is,
1674-
you cannot sort on an expression that is not included in the
1675-
<literal>DISTINCT</literal> list.
1684+
If <literal>DISTINCT</literal> is specified with an
1685+
<replaceable>order_by_clause</replaceable>, <literal>ORDER
1686+
BY</literal> expressions can only reference columns in the
1687+
<literal>DISTINCT</literal> list. For example:
1688+
<programlisting>
1689+
WITH vals (v) AS ( VALUES (1),(3),(4),(3),(2) )
1690+
SELECT array_agg(DISTINCT v ORDER BY v DESC) FROM vals;
1691+
array_agg
1692+
-----------
1693+
{4,3,2,1}
1694+
</programlisting>
16761695
</para>
16771696

1678-
<note>
1679-
<para>
1680-
The ability to specify both <literal>DISTINCT</literal> and <literal>ORDER BY</literal>
1681-
in an aggregate function is a <productname>PostgreSQL</productname> extension.
1682-
</para>
1683-
</note>
1684-
16851697
<para>
16861698
Placing <literal>ORDER BY</literal> within the aggregate's regular argument
16871699
list, as described so far, is used when ordering the input rows for

0 commit comments

Comments
 (0)