SQL Reference

Sorted Aggregate Functions

Overview

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. The syntax is:

SQL


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"