Ordering the List of Values Returned from LISTAGG

The LISTAGG function transforms non-null values from a group of rows into a list of values that are delimited by a configurable separator. LISTAGG can be used to de-normalize rows into a string of comma-separated values or other human-readable formats.

Example:

dbadmin=> SELECT * dbadmin-> FROM valrank; id | rank | valname ---+------+--------- 1 | 4 | D 1 | 2 | B 2 | 2 | B 2 | 3 | C 1 | 1 | A 1 | 3 | C 1 | 5 | E 2 | 1 | A (8 rows) dbadmin=> SELECT id, LISTAGG(rank) ranks, LISTAGG(valname) valnames dbadmin-> FROM valrank dbadmin-> GROUP BY id dbadmin-> ORDER BY id; id | ranks | valnames ---+-----------+----------- 1 | 2,4,1,3,5 | B,D,A,C,E 2 | 2,3,1 | B,C,A (2 rows) What if I want to order the list of values that LISTAGG returns? Unfortunately, there is not yet an ORDER BY parameter for the LISTAGG function (that’s coming). But in the meantime, maybe we can manually order the table data first before passing it into the LISTAGG function. For that we can use a sub-query with an ORDER BY. dbadmin=> SELECT id, LISTAGG(rank) ranks, LISTAGG(valname) valnames dbadmin-> FROM (SELECT * FROM valrank ORDER BY id, rank) foo dbadmin-> GROUP BY id dbadmin-> ORDER BY id; id | ranks | valnames ---+-----------+----------- 1 | 2,4,1,3,5 | B,D,A,C,E 2 | 2,3,1 | B,C,A (2 rows) That didn’t work! Hmm.

In a previous Vertica tip titled Sort Data from Within a View we learned how to force Vertica to sort the view data on the ORDER BY columns by adding a LIMIT clause. Maybe can use that same trick here? dbadmin=> SELECT id, LISTAGG(rank) ranks, LISTAGG(valname) valnames dbadmin-> FROM (SELECT * FROM valrank ORDER BY id, rank LIMIT 10) foo dbadmin-> GROUP BY id dbadmin-> ORDER BY id; id | ranks | valnames ---+-----------+----------- 1 | 1,2,3,4,5 | A,B,C,D,E 2 | 1,2,3 | A,B,C (2 rows) I’ll be darned. Now the LISTAGG transformed data is ordered!

UPDATE: As of Vertica 11.0.2, the LISTAGG function supports the WITHIN GROUP ORDER BY clause!

Helpful Links:

LISTAGG
Sort Data from Within a View: Quick Tip

Have fun!