
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!