LISTAGG, change sorting of result with reference to another column

1

Same question as sql - LISTAGG, change sorting of result with reference to another column - Stack Overflow

however, seems

SELECT LISTAGG("item", ', ') WITHIN GROUP (ORDER BY "Desc") "item"
FROM tableA;

is not working in Dremio, with error message

ORDER BY columns must be subset LISTAGG columns

I have some workarounds to use combination of CAST, CONCAT and ROW_NUMBER() OVER to preserve the order. Any simpler way to achieve the same result?

Thanks.

1 Like
2

I have the same issue, please advise

3

Workaround: for me it works to list_agg columns that are strings, but not columns that are numbers.

4

@BIANCXA That is correct, LIST_AGG currently works on string, ARRAY_AGG should work on both but in ARRAY_AGG, order by is not there

5

Great. However, even using strings, I cannot get it to work to sort by a different column than the one in the LISTAGG. Please advise.

6

@BIANCXA What version of Dremio is this? The order by clause for ARRARY_AGG should be there now

7

Version 24.3.9 on AZURE AKS