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…

OpenText profile picture
OpenText

January 11, 20192 minute read

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!

Share this post

Share this post to x. Share to linkedin. Mail to
OpenText avatar image

OpenText

OpenText, The Information Company, enables organizations to gain insight through market-leading information management solutions, powered by OpenText Cloud Editions.

See all posts

More from the author

Manutan combines digital services with the human touch to delight customers

Manutan combines digital services with the human touch to delight customers

At Manutan, we equip businesses and communities with the products and services they require to succeed. Headquartered in France, our company has three divisions, serving…

4 minute read

Reaching new markets in Europe and beyond

Reaching new markets in Europe and beyond

How information management specialists at One Fox slashed time to market for innovative products with OpenText Cloud Platform Services At One Fox, we’ve driven some…

4 minute read

SoluSoft helps government agencies tackle fraud faster

SoluSoft helps government agencies tackle fraud faster

Fraud, in all its forms, is a pervasive problem, spanning industries and preying on vulnerabilities in federal and state government systems. Each year in the…

3 minute read

Stay in the loop!

Get our most popular content delivered monthly to your inbox.