How to sort the result from string_agg()

sql

string_agg

postgresql

sql-queries

byNikita Barsukov·Aug 30, 2024

To sort your aggregated strings, you can use ORDER BY within the STRING_AGG() function:

STRING_AGG(column, ', ' ORDER BY column)

When using the table employees for example, to get a sorted list of last_names:

SELECT STRING_AGG(last_name, ', ' ORDER BY last_name) FROM employees;

Pre-sort approach with sub query

If you find yourself needing advanced sorting or ordering of multiple columns, a subquery to pre-order the data might be your answer:

SELECT STRING_AGG(last_name, ', ' ORDER BY order_column)
FROM (
    SELECT last_name, order_column
    FROM employees
    ORDER BY order_column 
) as better_order_than_Starbucks; -- Who says SQL doesn't serve Order?

This technique ensures custom sorting prior to aggregation. And for a better performance, look into updating your PostgreSQL that potentially offers more locomotion to your SQL operation!

Delicate considerations in pre-sorting

Data type ordering

When ordering columns of different data types, make sure they are explicitly cast to avoid unsortable column errors.

Complex ordering logic

Handle multifaceted ordering logic by accessorizing your queries with CASE statements or nested ordering:

SELECT STRING_AGG(last_name, ', ' ORDER BY CASE WHEN condition THEN order_column1 ELSE order_column2 END) 
FROM employees; -- When SQL logic hits harder than my mom's wooden spoon

Patterns to sort by

When needing to sort by patterns, use a subquery or CTE with necessary filtering to ensure only relevant rows make their way to the final roll call.

Keeping up with PostgreSQL

PostgreSQL evolves faster than a Pikachu with a Thunder Stone. Always keep an eye out for new features or updated syntax. Remember, it's a string_agg() we're catching not a wild squirtle.

The WITHIN GROUP clause

Introduced PostgreSQL 9.0, WITHIN GROUP can order the bento box of data before it gets passed around:

SELECT STRING_AGG(column, ', ' ORDER BY sort_column) WITHIN GROUP (ORDER BY sort_column) 
FROM table 
GROUP BY id; -- Packaging data as tidy as a Japanese lunch box.