Why is WITHIN GROUP ORDER BY clause not working with LISTAGG?

Raul

Hello!

The documentation shows that it should work: link

But I get a Syntax error when I try it in vsql v11.0.1-0:

dbadmin=> SELECT listagg(t.col) FROM (SELECT 'b' AS col UNION ALL SELECT 'c' AS col UNION ALL SELECT 'a' AS col) t;
 listagg
---------
 b,c,a
(1 row)

dbadmin=> SELECT listagg(t.col) WITHIN GROUP (ORDER BY t.col) FROM (SELECT 'b' AS col UNION ALL SELECT 'c' AS col UNION ALL SELECT 'a' AS col) t;
ERROR 4856:  Syntax error at or near "FROM" at character 53
LINE 1: ...LECT listagg(t.col) WITHIN GROUP (ORDER BY t.col) FROM (SELE...
                                                             ^
dbadmin=> SELECT VERSION();
               VERSION
-------------------------------------
 Vertica Analytic Database v11.0.1-0
(1 row)

Can someone confirm this please? Or am I doing something wrong?

Thank you!

Jim_Knicely

Hi,

I believe that it is new to Vertica 11.0.2 (which comes out very shortly)...

Let me check with the doc team to make sure that WITHIN GROUP

mosheg

v11.0.2 is now available

SELECT version();
               version
-------------------------------------
 Vertica Analytic Database v11.0.2-0
(1 row)

SELECT listagg(t.col) WITHIN GROUP (ORDER BY t.col) FROM (SELECT 'b' AS col UNION ALL SELECT 'c' AS col UNION ALL SELECT 'a' AS col) t;
 listagg
---------
 a,b,c
(1 row)

Jim_Knicely

Hah! I keep forgetting to press shift-refresh in Google Chrome to get an updated navigation panel on the left in the doc!