If I do
SELECT LISTAGG( COLUMN_VALUE ) WITHIN GROUP ( ORDER BY NULL ) AS OrderByNULL,
LISTAGG( COLUMN_VALUE ) WITHIN GROUP ( ORDER BY 1 ) AS OrderByCONST,
LISTAGG( COLUMN_VALUE ) WITHIN GROUP ( ORDER BY ROWNUM ) AS OrderByROWNUM
FROM TABLE( SYS.ODCIVARCHAR2LIST( '5', '222', '4' ) );
The output is:
ORDERBYNULL ORDERBYCONST ORDERBYROWNUM
----------- ------------ -------------
222,4,5 222,4,5 5,222,4
The query appears to have done an alphanumerical sort when using ORDER BY
with non-deterministic ordering (NULL
or a constant) and has maintained the input order when using ORDER BY ROWNUM
(deterministic).
The LISTAGG
documentation states that:
The order_by_clause determines the order in which the concatenated values are returned. The function is deterministic only if the ORDER BY column list achieved unique ordering.
And when looking at analytic functions it states that:
Whenever the order_by_clause results in identical values for multiple rows, the function behaves as follows: [...] For all other analytic functions, the result depends on the window specification. If you specify a logical window with the RANGE keyword, then the function returns the same result for each of the rows. If you specify a physical window with the ROWS keyword, then the result is nondeterministic.
So as far as I can tell from the documentation a non-deterministic ordering is to be expected - however, the function gives a deterministic output based on an alphanumerical sort and not on the order in which the rows are processed (which is the commonly held view).
This is different to the behaviour of other analytic functions (when using a physical window with the ROWS
keyword):
SELECT LAST_VALUE( COLUMN_VALUE )
OVER ( ORDER BY NULL ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )
AS BYNULL,
LAST_VALUE( COLUMN_VALUE )
OVER ( ORDER BY 1 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )
AS BYCONST,
LAST_VALUE( COLUMN_VALUE )
OVER ( ORDER BY ROWNUM ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )
AS BYROWNUM
FROM TABLE( SYS.ODCIVARCHAR2LIST( '5', '222', '4' ) );
Which gives a consistent output for all the different orderings:
BYNULL BYCONST BYROWNUM
------ ------- --------
4 4 4
4 4 4
4 4 4
Is there official documentation for how an ordering is applied when LISTAGG
is supplied a non-deterministic ordering?
Note: The behaviour of ORDER BY NULL
is commented on here stating:
In this example, the elements have been aggregated alphabetically, despite the NULL ordering clause. This appears to be the default behaviour when using a constant ORDER BY expression
But this is just an comment on the behaviour in a non-Oracle site.
using ORDER BY NULL is a workaround that satifies the syntax requirement but does not actually change the order of the data. In effect it is an instruction to not order at all. N.B.: some (myself included) prefer to use SELECT 1 instead of SELECT NULL but there is no difference in effect.
About LISTAGG. The LISTAGG function is used to aggregate a set of string values within a group into a single string by appending the string-expression values based on the order that's specified in the 'WITHIN GROUP' clause. As a single-set aggregate function, LISTAGG operates on all rows and returns a single output row ...
An Oracle LISTAGG Function is an aggregate function that returns a single row. This is used to transform data from multiple rows into a single list of values separated by a given delimiter. It operates on all rows and returns single. It returns a comma or other delimiter separatedresult set just like an excel CSV file.
Listagg is an ordered set function, which require the within group clause to specify an order. The minimal syntax is: LISTAGG(<expression>, <separator>) WITHIN GROUP(ORDER BY …)
Possibly the order will depend on many factor, including the execution plan of the query, the configuration of your instance, the version of the database, if you are on exadata or not.
If you are not giving oracle any specific criteria to order the data you can't count on any specific ordering, even if from your trials you seem to get the data consistently sorted in a specific way.
According to Oracle documentation for listagg:
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With