MIN_BY aggregate function to find a minimum value and return a corresponding value - MaxCompute

Returns the value of one column from the row where another column reaches its minimum. For example, given columns ename and sal, MIN_BY(ename, sal) returns the name of the lowest-paid employee.

MIN_BY is an extension function introduced in MaxCompute V2.0. It provides the same functionality as ARG_MIN, with a different parameter order. This difference in parameter order preserves compatibility with open-source SQL syntax.

Usage notes

MIN_BY accepts any data type for both parameters. If your query uses new data types — TINYINT, SMALLINT, INT, FLOAT, VARCHAR, TIMESTAMP, or BINARY — enable the MaxCompute V2.0 data type edition before running the query.

Session level (applies to the current session only): Add the following SET statement before your SQL statement and run them together.

set odps.sql.type.system.odps2=true;

Project level (takes 10 to 15 minutes to take effect): Run the following command as the project owner.

setproject odps.sql.type.system.odps2=true;

For more information about setproject, see Project operations. For the precautions that apply when enabling the MaxCompute V2.0 data type edition at the project level, see Data type editions.

If an SQL statement contains multiple aggregate functions and your project has insufficient resources, memory overflow may occur. Optimize the SQL statement or purchase additional compute resources as needed.

Syntax

min_by(<valueToReturn>, <valueToMinimize>)

Parameters

Parameter	Required	Data type	Description
`valueToReturn`	Yes	Any	The column whose value is returned.
`valueToMinimize`	Yes	Any	The column used to determine which row has the minimum value.

Return value

Returns the same data type as valueToReturn.

If multiple rows share the same minimum valueToMinimize, one of the corresponding valueToReturn values is returned at random. The result may differ across executions.
Rows where valueToMinimize is null are excluded from the calculation.

Sample data

The following examples use the emp table. Create the table and load the sample data with the following statements:

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;

The emp.txt file contains the following data:

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 the entire table.

select min_by(ename, sal) from emp;

Result:

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

Example 2: Return the name of the lowest-paid employee in each department, grouped by deptno.

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

Result:

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

Related functions

MIN_BY is an aggregate function. For other aggregate functions, see Aggregate functions. For the equivalent function with a different parameter order, see ARG_MIN.