ARG_MIN function to return a value from a row containing a minimum value - MaxCompute

Returns the value of valueToReturn from the row where valueToMinimize is smallest. For example, given a table with employee and salary columns, arg_min(salary, employee) returns the name of the lowest-paid employee.

ARG_MIN is an aggregate function introduced in MaxCompute V2.0.

Syntax

arg_min(<valueToMinimize>, <valueToReturn>)

Parameters

Parameter	Required	Data type	Description
`valueToMinimize`	Yes	Any	The column or expression used to find the minimum value. This is the sort key — the function scans all rows and identifies the one with the smallest value in this column.
`valueToReturn`	Yes	Any	The column or expression whose value is returned from the row with the minimum `valueToMinimize`.

Return value

The return value has the same data type as valueToReturn.

Ties: If multiple rows share the smallest valueToMinimize, the function returns the valueToReturn from one of those rows at random. Results may differ across executions.
NULL handling: Rows where valueToMinimize is NULL are excluded from the calculation.

Usage notes

MaxCompute V2.0 data types: If valueToMinimize or valueToReturn uses a V2.0-only data type (TINYINT, SMALLINT, INT, FLOAT, VARCHAR, TIMESTAMP, or BINARY), enable the MaxCompute V2.0 data type edition first:
- Session level: Add set odps.sql.type.system.odps2=true; before your SQL statement, then submit both together.
- Project level: The project owner runs:
```
setproject odps.sql.type.system.odps2=true;
```
  The setting takes effect after 10 to 15 minutes. For details, see Project operations and Data type editions.
Memory overflow: SQL statements that include multiple aggregate functions may cause memory overflow when project resources are limited. Optimize the SQL statement or purchase additional computing resources based on your requirements.

Sample data

The examples in this topic use a table named emp. Create the table and load sample data:

create table if not exists emp
   (empno  BIGINT,
    ename  STRING,
    job    STRING,
    mgr    BIGINT,
    hiredate DATETIME,
    sal    BIGINT,
    comm   BIGINT,
    deptno BIGINT);
tunnel upload emp.txt emp; -- Replace emp.txt with the actual path to your data file.

The emp.txt file contains the following records:

7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20
7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30
7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30
7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,,20
7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30
7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30
7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10
7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20
7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10
7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30
7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20
7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30
7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20
7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,,10
7948,JACCKA,CLERK,7782,1981-04-12 00:00:00,5000,,10
7956,WELAN,CLERK,7649,1982-07-20 00:00:00,2450,,10
7956,TEBAGE,CLERK,7748,1982-12-30 00:00:00,1300,,10

Examples

Example 1: Return the name of the employee with the lowest salary across all departments.

select arg_min(sal, ename) from emp;

Result:

+------------+
| _c0        |
+------------+
| SMITH      |
+------------+

SMITH has the uniquely lowest salary (800) in this dataset. If multiple employees shared the lowest salary, the function would return one of their names at random and results could differ across executions.

Example 2: Use GROUP BY to return the lowest-paid employee in each department.

select deptno, arg_min(sal, ename) from emp group by deptno;

Result:

+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | MILLER     |
| 20         | SMITH      |
| 30         | JAMES      |
+------------+------------+

Example 3: Demonstrate NULL handling — rows where valueToMinimize is NULL are excluded.

In the sample data, the comm column is NULL for most employees. The following query returns the name of the employee with the lowest commission, excluding rows where comm is NULL:

select arg_min(comm, ename) from emp;

Result:

+------------+
| _c0        |
+------------+
| TURNER     |
+------------+

TURNER has comm = 0, which is the lowest non-NULL commission value. Rows with comm = NULL are excluded from the calculation.

Related functions

ARG_MIN is an aggregate function. For other aggregate functions, see Aggregate functions.