Skip to main content
These functions can be applied to aggregate data. They include:

​
Lookup/Grouping Functions

ARG_MAX(agg_expr, ret_expr)

The value of ret_expr where agg_expr is the maximum value (e.g. ARG_MAX(cost, product_id) returns the product ID of the highest cost product). ARG_MAX(a, b) is equivalent to LAST(b, a).

ARG_MIN(agg_expr, ret_expr)

The value of ret_expr where agg_expr is the minimum value (e.g. ARG_MIN(cost, product_id) returns the product ID of the lowest cost product). ARG_MIN(a, b) is equivalent to FIRST(b, a).

ATTR(expr)

If MIN(expr) = MAX(expr), returns expr; otherwise *
expr must resolve to a string type, and must be casted to one, if not already

FIRST(ret_expr, agg_expr)

The value of ret_expr where agg_expr is the minimum value (e.g. FIRST(product_id, cost) returns the product ID ID of the lowest cost product). FIRST(a, b) is equivalent to ARG_MIN(b,a)

GROUPING(expr)

Used primarily with ROLLUP, CUBE, and GROUPING SETS, to distinguish the source of null values in an aggregated result set, returns whether expr is part of the aggregation set used to calculate the values in a given result set row. Returns 0 if expr is part of the row’s aggregation set, 1 if expr is not (meaning that aggregation took place across all expr values).For example, in a ROLLUP(A) operation, there will be two potential rows with null in the result set for column A. One row will contain null values of A aggregated together, and the other will contain null, but be an aggregation over the entire table, irrespective of A values. In this case, GROUPING(A) will return 0 for the null values of A aggregated together (as well as all other grouped A values) and 1 for the row resulting from aggregating across all A values.

LAST(ret_expr, agg_expr)

The value of ret_expr where agg_expr is the maximum value (e.g. LAST(product_id, cost) returns the product ID of the highest cost product). LAST(a, b) is equivalent to ARG_MAX(b, a).