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

RaulRaul Vertica Customer

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 - Select Field - 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 Vertica Employee 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 - Select Field - 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