ARGMAX_AGG

Takes two arguments target and arg, where both are columns or column expressions in the queried dataset.

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.
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.

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

ARGMIN_AGG