How to sort the result from string_agg()

Question

I have a table:

CREATE TABLE tblproducts
(
productid integer,
product character varying(20)
)

With the rows:

INSERT INTO tblproducts(productid, product) VALUES (1, 'CANDID POWDER 50 GM');
INSERT INTO tblproducts(productid, product) VALUES (2, 'SINAREST P SYP 100 ML');
INSERT INTO tblproducts(productid, product) VALUES (3, 'ESOZ D 20 MG CAP');
INSERT INTO tblproducts(productid, product) VALUES (4, 'HHDERM CREAM 10 GM');
INSERT INTO tblproducts(productid, product) VALUES (5, 'CREAM 15 GM');
INSERT INTO tblproducts(productid, product) VALUES (6, 'KZ LOTION 50 ML');
INSERT INTO tblproducts(productid, product) VALUES (7, 'BUDECORT 200 Rotocap');

If I execute string_agg() on tblproducts:

SELECT string_agg(product, ' | ') FROM "tblproducts"

It will return the following result:

CANDID POWDER 50 GM | ESOZ D 20 MG CAP | HHDERM CREAM 10 GM | CREAM 15 GM | KZ LOTION 50 ML | BUDECORT 200 Rotocap

How can I sort the aggregated string, in the order I would get using ORDER BY product?

I'm using PostgreSQL 9.2.4.

score 328 · Accepted Answer · edited Apr 24 '19 at 20:58

328

With postgres 9.0+ you can write:

select string_agg(product,' | ' order by product) from "tblproducts"

Details here.

edited Apr 24 '19 at 20:58

KT12

549
11
24

answered Jul 23 '14 at 09:42

Ihor Romanchenko

26,995
8
48
44

can you please suggest a solution which will also work when using window functions? – Saurabh Gujarani May 08 '18 at 05:37
Thanks for the link. Searching `string_agg` in the documentation doesn’t take you there. – Manngo Aug 18 '19 at 05:53
Is it possible to keep the resulted output as it is without sorting them? – Shantilal Suthar Feb 14 '23 at 11:46

Luuk · Answer 2 · 2022-05-24T17:48:07.147

169

For Microsoft SQL: Use "WITHIN GROUP"

https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017

SELECT
  STRING_AGG(prod, '|') WITHIN GROUP (ORDER BY product)
FROM ...

edited May 24 '22 at 17:48

answered Jan 21 '19 at 15:28

Luuk

12,245
5
22
33

7

The question was about PostgreSQL. The `WITHIN GROUP` clause does not apply to the `string_agg` function, as it does with Microsoft SQL. – Manngo Aug 18 '19 at 06:04
66

The question was about string_agg. Postgres was incidental to his question and he mentioned it last. The question is useful to others as well. – nomen Mar 05 '20 at 17:42
3

If this syntax gives you syntax errors, check your compatibility level: https://stackoverflow.com/questions/43611024/issue-with-string-agg-within-group-and-visual-studio – Mr. TA Jul 13 '20 at 13:32

score 3 · Answer 3 · answered Jun 11 '22 at 16:08

3

This seems to sort numerically even with casting:

select string_agg(cast (o.id as varchar),',' order by o.id) from tb_organisation o 
    inner join tb_country c on o.country_fk = c.id 
        where c.name ilike '%united%' group by c.id;

answered Jun 11 '22 at 16:08

gil.fernandes

12,978
5
63
76

score 2 · Answer 4 · answered Jul 23 '14 at 09:37

2

select string_agg(prod,' | ') FROM 
  (SELECT product as prod FROM tblproducts ORDER BY product )MAIN;

SQL FIDDLE

answered Jul 23 '14 at 09:37

Ilesh Patel

2,053
16
27

4

I had the same problem as OP, and this approach was my first thought, but unfortunately it doesn't work (which brought me here), whereas Igor's does. – chbrown Feb 04 '16 at 14:13
On my side, both approaches (Ilesh's and Igor's) worked. – Stephan Sep 15 '16 at 09:16
3

Wrong answer. It might work but is not guaranteed to work. – zyamys Mar 15 '18 at 21:13
1

Relational Database is based in part on mathematical sets, and this is reflected in the fact that a basic principle in SQL is that row order is not significant. Even if you were to include an `ORDER BY` clause in the sub query, the `FROM` clause doesn’t necessarily get the data in order. If this works, it is pure luck. – Manngo Aug 18 '19 at 07:47
I was happily using this solution for a long time, until one day some data entered my system that caused this to no longer work. It's very deceptive. (though to Ilesh's defence the docs do say it will "usually" work: "Alternatively, supplying the input values from a sorted subquery will usually work") – fei0x Jun 30 '21 at 17:05

How to sort the result from string_agg()

4 Answers4

Linked