Ordering the List of Values Returned from LISTAGG

Jim_Knicely · January 2019

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!

Helpful Links:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Aggregate/LISTAGG.htm

https://www.vertica.com/blog/sort-data-from-within-a-view-quick-tip/

Have fun!

Special thanks to @DaveT for collaborating with me on this tip!