ARGMIN_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. ARGMIN_AGG finds the row with the lowest non-null value in target and returns the value of arg in that row. If multiple rows contain the lowest target value, ARGMIN_AGG returns arg from the first row that it finds. Use the WITHIN GROUP ORDER BY clause to control which row ARGMMIN_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

ARGMIN_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 lowest target value; otherwise, results are likely to vary with each iteration of the same query.

Examples

The following example calls ARGMIN_AGG in a WITH clause to find the lowest salary among all employees in each region, and returns with the lowest-paid employee IDs. The primary query returns with the salary amounts and employee names:

=> WITH msr (employee_region, emp_id) AS
    (SELECT employee_region, argmin_agg(annual_salary, employee_key) lowest_paid_employee FROM employee_dim GROUP BY employee_region)
    SELECT msr.employee_region, ed.annual_salary AS lowest_salary, ed.employee_first_name||' '||ed.employee_last_name AS employee_name
     FROM msr JOIN employee_dim ed ON msr.emp_id = ed.employee_key ORDER BY annual_salary DESC;
         employee_region          | lowest_salary |  employee_name
----------------------------------+---------------+-----------------
 NorthWest                        |         20913 | Raja Garnett
 SouthWest                        |         20750 | Seth Moore
 West                             |         20443 | Midori Taylor
 South                            |         20363 | David Bauer
 East                             |         20306 | Craig Jefferson
 MidWest                          |         20264 | Dean Vu
(6 rows)

See also

ARGMAX_AGG