ARRAY_AGG returns an ARRAY type value whose elements are the aggregated set of element expression values that were passed in as the first parameter. For an n-D ARRAY result value, the value_expression values that are aggregated into the array are entered in row-major order.
If you specify the ORDER BY clause in the first argument, then the values input to the resultant array are ordered by the column values specified in this clause. The following rules apply to the ORDER BY clause of ARRAY_AGG:
- Only one value may be passed in the ORDER BY clause of ARRAY_AGG. This is different from an ORDER BY in a SELECT statement, where multiple values may be passed for ORDER BY.
- An ARRAY column value may not be passed as the sort key in the ORDER BY clause. You cannot make relational comparisons of ARRAY values. Any other data type that cannot be compared, such as a BLOB or CLOB, may not be passed as the sort key in the ORDER BY clause.
- For the single value passed to ORDER BY, any expression that may be relationally comparable may be passed. However, if the value passed is not a column value, then sorting of the resultant array elements will not occur and the results will be written to the resultant array in random order. Use a column value from one of the referenced tables in the SQL statement for proper sorting to occur.