diff options
| author | Tom Lane <tgl@sss.pgh.pa.us> | 2010-08-04 15:27:57 +0000 |
|---|---|---|
| committer | Tom Lane <tgl@sss.pgh.pa.us> | 2010-08-04 15:27:57 +0000 |
| commit | 72150db0c5d87143c68efbf555648e3e55685535 (patch) | |
| tree | cc731ced9f404f3cb690b6a990904b7c7e8e6b16 | |
| parent | 145343534c153d1e6c3cff1fa1855787684d9a38 (diff) | |
| download | postgresql-72150db0c5d87143c68efbf555648e3e55685535.tar.gz postgresql-72150db0c5d87143c68efbf555648e3e55685535.zip | |
Add an example to clarify the use of ORDER BY in multiple-argument
aggregates. People seem to not get this right without help.
| -rw-r--r-- | doc/src/sgml/syntax.sgml | 20 |
1 files changed, 17 insertions, 3 deletions
diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml index fcf5f161d8b..16aa89f497f 100644 --- a/doc/src/sgml/syntax.sgml +++ b/doc/src/sgml/syntax.sgml @@ -1,4 +1,4 @@ -<!-- $PostgreSQL: pgsql/doc/src/sgml/syntax.sgml,v 1.148 2010/07/20 00:34:44 rhaas Exp $ --> +<!-- $PostgreSQL: pgsql/doc/src/sgml/syntax.sgml,v 1.149 2010/08/04 15:27:57 tgl Exp $ --> <chapter id="sql-syntax"> <title>SQL Syntax</title> @@ -1567,7 +1567,7 @@ sqrt(2) unspecified order. In many cases this does not matter; for example, <function>min</> produces the same result no matter what order it receives the inputs in. However, some aggregate functions - (such as <function>array_agg</> and <function>xmlagg</>) produce + (such as <function>array_agg</> and <function>string_agg</>) produce results that depend on the ordering of the input rows. When using such an aggregate, the optional <replaceable>order_by_clause</> can be used to specify the desired ordering. The <replaceable>order_by_clause</> @@ -1575,13 +1575,27 @@ sqrt(2) described in <xref linkend="queries-order">, except that its expressions 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; </programlisting> </para> <para> + When dealing with multiple-argument aggregate functions, note that the + <literal>ORDER BY</> clause goes after all the aggregate arguments. + For example, this: +<programlisting> +SELECT string_agg(a, ',' ORDER BY a) FROM table; +</programlisting> + not this: +<programlisting> +SELECT string_agg(a ORDER BY a, ',') FROM table; -- not what you want +</programlisting> + The latter syntax will be accepted, but <literal>','</> will be + treated as a (useless) sort key. + </para> + + <para> If <literal>DISTINCT</> is specified in addition to an <replaceable>order_by_clause</>, then all the <literal>ORDER BY</> expressions must match regular arguments of the aggregate; that is, |