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, 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!

Have fun!