Aggregation

How do you sort the values inside STRING_AGG?

By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17

Put ORDER BY inside the parentheses, after the delimiter: string_agg(city, ', ' ORDER BY city). There is no comma before ORDER.

The ORDER BY at the end of a query sorts the rows the query returns. It cannot reach inside an aggregate, and putting it there instead raises an error about GROUP BY that says nothing about ordering. The same inner clause works in jsonb_agg and array_agg.

Every example below reads customers, one row per customer, with a country and a city.

You can edit and run two of the queries below in your browser, and there are two exercises at the end. See our other guides to aggregating query output for the rest of this family.

Schema · ecommerce1 table? = nullable

customers

idinteger

nametext

emailtext

city?text

countrytext

created_attimestamptz

is_activeboolean

SELECT count(*) AS customer_rows,
       count(DISTINCT country) AS countries,
       count(DISTINCT city) AS cities
FROM customers;

customer_rows	countries	cities
70	22	46

the customers table in our sandbox

Where exactly does ORDER BY go inside STRING_AGG?

After the delimiter, still inside the parentheses, with no comma separating it. Two country groups, each holding a city more than once:

SELECT country, string_agg(city, ', ' ORDER BY city) AS cities
FROM customers
WHERE country IN ('DE', 'GB')
GROUP BY country
ORDER BY country;

country	cities
DE	Berlin, Berlin, Munich
GB	Birmingham, Edinburgh, London, London, London, London, Manchester

two country groups with the cities sorted inside the aggregate

Take the clause out and the same values come back in whatever order the plan happened to produce. Here that is neither the order they were stored in nor any order the query asked for, because grouping two countries sorts the rows on the way through:

SELECT country, string_agg(city, ', ') AS cities
FROM customers
WHERE country IN ('DE', 'GB')
GROUP BY country
ORDER BY country;

country	cities
DE	Berlin, Munich, Berlin
GB	London, Manchester, Birmingham, London, Edinburgh, London, London

the same rows with no sort inside the aggregate. Berlin and London are no longer grouped together, and the British group lists seven cities for eight customers because one is null

SELECT country, string_agg(city, ', ' ORDER BY city) AS cities
FROM customers
WHERE country IN ('DE', 'GB')
GROUP BY country
ORDER BY country;

Why does ORDER BY at the end of the query give a GROUP BY error?

Because after grouping, the column no longer exists as a single value to sort by. Each returned row stands for a whole group, and city holds several values inside it:

SELECT country, string_agg(city, ', ') AS cities
FROM customers
WHERE country IN ('DE', 'GB')
GROUP BY country
ORDER BY city;

PostgreSQL responds column "customers.city" must appear in the GROUP BY clause or be used in an aggregate function

The error names GROUP BY rather than ordering, which is why this one costs people time. The two clauses do different jobs: the inner ORDER BY sorts the values being joined into one string, and the outer one sorts the rows the query hands back. A query can carry both at once, and the first example on this page does: it sorts the cities inside each string and the countries between the rows.

Does the same ORDER BY work in json_agg and array_agg?

Yes, in the same position and with the same meaning. These three collectors differ in what they return, not in how they sort:

SELECT string_agg(city, ', ' ORDER BY city) AS as_text,
       jsonb_agg(city ORDER BY city)::text AS as_json,
       array_agg(city ORDER BY city)::text AS as_array
FROM customers
WHERE country = 'DE';

as_text	as_json	as_array
Berlin, Berlin, Munich	["Berlin", "Berlin", "Munich"]	{Berlin,Berlin,Munich}

one inner ORDER BY, three return types

jsonb_agg and array_agg take no delimiter, so the ORDER BY follows the value directly. Both are cast to text here so the brackets and braces are visible; the difference between a JSON array and a Postgres array matters once an application parses it.

What happens if you leave ORDER BY out?

The values arrive in whatever order the query produced them, and that order is not promised. PostgreSQL's documentation states it directly: these aggregates produce meaningfully different result values depending on the order of the input values, and the order is only specified when the call says so.

A small table on a quiet database will hand back the same order every time, which is what makes this one dangerous. The order can change when the data grows or when an index is added. Write the clause whenever the order matters.

Can you sort by a different column than the one you are aggregating?

Yes. The sort expression is independent of the aggregated value, so a list of cities can be ordered by the customer id that produced each one:

SELECT country, string_agg(city, ', ' ORDER BY id DESC) AS cities
FROM customers
WHERE country = 'DE'
GROUP BY country;

country	cities
DE	Munich, Berlin, Berlin

cities ordered by customer id, highest first

Try it: change ORDER BY id DESC to ORDER BY id and run it again. The same three cities come back in the opposite order.

SELECT country, string_agg(city, ', ' ORDER BY id DESC) AS cities
FROM customers
WHERE country = 'DE'
GROUP BY country;

How do you combine DISTINCT and ORDER BY?

Sort by the same expression you are aggregating. With DISTINCT in the call, a sort key that is not in the argument list is rejected:

SELECT string_agg(DISTINCT city, ', ' ORDER BY id) AS cities
FROM customers
WHERE country = 'DE'
GROUP BY country;

PostgreSQL responds in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list

The rule is syntactic rather than a consequence of de-duplication: under DISTINCT, the sort key has to be one of the expressions in the argument list, and id is not. It holds even for a sort key that collapses nothing, such as length(city). Sort by city, the thing being collected, and the call runs:

SELECT string_agg(DISTINCT city, ', ' ORDER BY city) AS cities
FROM customers
WHERE country = 'DE'
GROUP BY country;

cities
Berlin, Munich

two distinct German cities, sorted by the value being collected

Practice: ordering inside an aggregate

string_agg is node N050 of our free SQL course, and JSON aggregation is N054. N050 covers this clause alongside the function and practises it in every one of its problems. N054 covers it in the lesson. No account, nothing to install, nothing to pay.