ARGMIN_AGG

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.

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