From 72150db0c5d87143c68efbf555648e3e55685535 Mon Sep 17 00:00:00 2001
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Wed, 4 Aug 2010 15:27:57 +0000
Subject: [PATCH] Add an example to clarify the use of ORDER BY in
 multiple-argument aggregates.  People seem to not get this right without
 help.

---
 doc/src/sgml/syntax.sgml | 20 +++++++++++++++++---
 1 file changed, 17 insertions(+), 3 deletions(-)

diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml
index fcf5f161d8..16aa89f497 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,12 +1575,26 @@ 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</>
-- 
2.40.0