The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
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
    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 Administrator
    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
    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