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