Skip site navigation (1) Skip section navigation (2)

Re: string_agg delimiter having no effect with order by

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: Thom Brown <thom(at)linux(dot)com>, pgsql-bugs <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: string_agg delimiter having no effect with order by
Date: 2010-08-04 15:29:39
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-bugspgsql-hackers
Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> Oh, yeah.  I guess you need this:

> select thing, string_agg(stuff, ',' order by stuff) from agg_test
> group by thing;

> Rather than this:

> select thing, string_agg(stuff order by stuff, ',') from agg_test
> group by thing;

> It's all kinds of not obvious to me what the second one is supposed to
> mean, but I remember this was discussed before.  Perhaps we need a
> <note> somewhere about multi-argument aggregates.

+    <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>

			regards, tom lane

In response to


pgsql-hackers by date

Next:From: Joshua TolleyDate: 2010-08-04 16:17:35
Subject: Re: GROUPING SETS revisited
Previous:From: Simon RiggsDate: 2010-08-04 15:26:30
Subject: Re: merge command - GSoC progress

pgsql-bugs by date

Next:From: Tom LaneDate: 2010-08-04 16:37:23
Subject: Re: In 8.2, shutdown wrongly caused automatic restart
Previous:From: ShineDate: 2010-08-04 14:53:02
Subject: BUG #5598: Compatibility modes

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group