ARGMAX_AGG

Takes two arguments target and arg, where both are columns or column expressions in the queried dataset. ARGMAX_AGG finds the row with the highest non-null value in target and returns the value of arg in that row. If multiple rows contain the highest target value, ARGMAX_AGG returns arg from the first row that it finds. Use the WITHIN GROUP ORDER BY clause to control which row ARGMAX_AGG finds first.

Behavior type

Immutable if the WITHIN GROUP ORDER BY clause specifies a column or set of columns that resolves to unique values within the group; otherwise Volatile.

Syntax

ARGMAX_AGG ( target, arg ) [ within-group-order-by-clause ]

Arguments

target, arg

Columns in the queried dataset.

Note

The target argument cannot reference a spatial data type column, GEOMETRY or GEOGRAPHY.

within-group-order-by-clause

Sorts target values within each group of rows:

WITHIN GROUP (ORDER BY { column-expression[ sort-qualifiers ] }[,...])

sort-qualifiers:

   { ASC | DESC [ NULLS { FIRST | LAST | AUTO } ] }

Use this clause to determine which row is returned when multiple rows contain the highest target value; otherwise, results are likely to vary with each iteration of the same query.

Tip

WITHIN GROUP ORDER BY can consume a large amount of memory per group. To minimize memory consumption, create projections that support GROUPBY PIPELINED.

Examples

The following example calls ARGMAX_AGG in a WITH clause to find which employees in each region are at or near retirement age. If multiple employees within each region have the same age, ARGMAX_AGG chooses the employees with the highest salary level and returns with their IDs. The primary query returns with details on the employees selected from each region:

=> WITH r AS (SELECT employee_region, ARGMAX_AGG(employee_age, employee_key)
       WITHIN GROUP (ORDER BY annual_salary DESC) emp_id
       FROM employee_dim GROUP BY employee_region ORDER BY employee_region)
    SELECT r.employee_region, ed.annual_salary AS highest_salary, employee_key,
       ed.employee_first_name||' '||ed.employee_last_name AS employee_name, ed.employee_age
       FROM r JOIN employee_dim ed ON r.emp_id = ed.employee_key ORDER BY ed.employee_region;
         employee_region          | highest_salary | employee_key |  employee_name   | employee_age
----------------------------------+----------------+--------------+------------------+--------------
 East                             |         927335 |           70 | Sally Gauthier   |           65
 MidWest                          |         177716 |          869 | Rebecca McCabe   |           65
 NorthWest                        |         100300 |         7597 | Kim Jefferson    |           65
 South                            |         196454 |          275 | Alexandra Harris |           65
 SouthWest                        |         198669 |         1043 | Seth Stein       |           65
 West                             |         197203 |          681 | Seth Jones       |           65
(6 rows)

See also