Sorted Aggregate Functions
Aggregate functions compute a single result from a group of rows. These aggregate functions support both DISTINCT which filters out repeated values, and also the standard ORDER BY syntax, which dictates the ordering of the aggregation.
Syntax
The ORDER BY syntax clause must be after all arguments to the aggregate function.
Function | Syntax | Purpose |
|---|---|---|
ARRAY_AGG | ARRAY_AGG(expr) | Returns an array containing every row from the expression. SELECT array_agg(c1 ORDER BY c1 DESC) FROM sys.dummy10 Returns: int[10,9,8,7,6,5,4,3,2,1] |
STRING_AGG | STRING_AGG(expr, delimiter) | Returns a string concatenated from every row from the expression. The delimiter argument is optional. SELECT string_agg(char(c1), '|' ORDER BY c1 ASC) FROM sys.dummy10 Returns: "1|2|3|4|5|6|7|8|9|10" |