MaxCompute:ARG_MAX

Usage notes

• Non-deterministic when tied: If multiple rows share the maximum value, the function returns a value from one of those rows at random. Results may differ across executions.

• NULL rows are excluded: Rows where valueToMaximize is NULL are not included in the calculation.

• New data types require V2.0 data type edition: If your query uses TINYINT, SMALLINT, INT, FLOAT, VARCHAR, TIMESTAMP, or BINARY, enable the MaxCompute V2.0 data type edition before running the query:

  • Session level: Add set odps.sql.type.system.odps2=true; before your SQL statement and commit them together.

  • Project level: Run the following command as the project owner. Changes take effect after 10 to 15 minutes.

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

    For details on setproject, see Project operations. For precautions when enabling the V2.0 data type edition at the project level, see Data type editions.

• Memory overflow risk: SQL statements with multiple aggregate functions may cause memory overflow if computing resources are insufficient. Optimize the SQL statement or purchase additional computing resources as needed.

Syntax

arg_max(<valueToMaximize>, <valueToReturn>)

Parameters

Return value

The return type matches the type of valueToReturn. If multiple rows have the same maximum value, one result is returned at random.

Examples

The following examples use the emp table. To create the table and load the sample data, run:

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

select arg_max(sal, ename) from emp;

+------------+
| _c0        |
+------------+
| KING       |
+------------+

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

+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | KING       |
| 20         | SCOTT      |
| 30         | BLAKE      |
+------------+------------+

-- The emp table includes rows with NULL comm values.
-- arg_max ignores those rows when comm is used as valueToMaximize.
select arg_max(comm, ename) from emp;

+------------+
| _c0        |
+------------+
| MARTIN     |
+------------+

Related functions

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