Add an example to clarify the use of ORDER BY in multiple-argument

tglsfdc · tglsfdc · commit 72150db0c5d8 · 2010-08-04T15:27:57.000Z
aggregates.  People seem to not get this right without help.
diff --git 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,20 +1567,34 @@ 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</>
     has the same syntax as for a query-level <literal>ORDER BY</> clause, as
     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</>
