Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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

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!

Best Answers

  • Jim_KnicelyJim_Knicely Administrator
    Accepted Answer

    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

  • moshegmosheg Employee
    Accepted Answer

    v11.0.2 is now available :smile:

    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_KnicelyJim_Knicely Administrator
    Accepted Answer

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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.