Sorted Aggregate Functions

aggregate functions compute a single result from a group of rows these aggregate functions support the distinct keyword, which filters out repeated values, and the standard order by syntax, which dictates the ordering of the aggregation to sort the elements of an array, use the docid\ ti3mdibvgmuudmlqu9xpl function during data load syntax aggregate(\[distinct] arg1, arg2, \[order by ]) substitute aggregate with the function name the order by syntax clause must be after all arguments to the aggregate function function name syntax purpose array agg array agg(expr) returns an array containing every row from the expression example select array agg(c1 order by c1 desc) from sys dummy10 output int\[10,9,8,7,6,5,4,3,2,1] array concat agg array concat agg(expr) returns an array that concatenates arrays across rows the input argument is a sql expression the element type must be consistent across rows the function ignores null inputs and returns null only when all inputs are null the distinct keyword removes duplicate arrays to remove duplicate elements from an array, use the array distinct function during load the order by syntax controls the order of concatenation across rows to sort elements in the array, use the array sort function during load example select array concat agg(c1 order by c1 asc) from sys dummy10 output int\[1,2,3,4,5,6,7,8,9,10] string agg string agg(expr, delimiter) returns a string concatenated from every row from the expression the delimiter argument is optional example select string agg(char(c1), '|' order by c1 asc) from sys dummy10 output "1|2|3|4|5|6|7|8|9|10" related links docid\ roka1ck6hndmod1smej1s docid\ ti3mdibvgmuudmlqu9xpl