DocsICP FilingConsole
Documentation
Home Page MaxCompute Developer Reference SQL developer reference Function reference Built-in functions Aggregate Functions MAX_BY

MAX_BY

Updated at:
Copy as MD
Product
My Favorites

The MAX_BY function returns the value from the valueToReturn column that corresponds to the maximum value in the valueToMaximize column. This function is an extension function in MaxCompute V2.0.

Syntax

MAX_BY(<valueToReturn>, <valueToMaximize>)

Introduction

The MAX_BY function finds the row with the maximum value for valueToMaximize and returns the corresponding value of valueToReturn.

MAX_BY has the same functionality as ARG_MAX, but the parameters are in a different order. MAX_BY is compatible with open source syntax.

Using multiple aggregate functions in a single SQL statement can cause a memory overflow if the project has insufficient compute resources. You can optimize the SQL statement or purchase more compute resources as needed.

Parameters

  • valueToMaximize: Required. This parameter can be of any data type. It specifies the column or expression used for comparison to determine the target row.

  • valueToReturn: Required. This parameter can be of any data type. It specifies the value to retrieve and return from the target row.

Note

If the function uses new data types such as TINYINT, SMALLINT, INT, FLOAT, VARCHAR, TIMESTAMP, or BINARY, you must enable V2.0 data types before you use the extension function. To do this, run one of the following commands:

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

  • Project level: The project owner can run the setproject odps.sql.type.system.odps2=true; command to enable the V2.0 data types for the entire project.

Return value

The return value has the same data type as valueToReturn. If multiple rows share the same maximum value, a value from one of these rows is returned non-deterministically. Rows where valueToMaximize is NULL are not included in the calculation.

Examples

Sample data

  1. Create a table named dept_sal.

    CREATE TABLE IF NOT EXISTS dept_sal (
 deptno BIGINT,
 ename STRING,
 sal BIGINT
 );

  2. Insert test data.

    INSERT INTO dept_sal VALUES
      (10, 'KING',   5000),
      (10, 'CLARK',  2450),
      (10, 'MILLER', 1300),
      (20, 'SCOTT',  3000),
      (20, 'FORD',   3000),
      (20, 'JONES',  2975),
      (30, 'BLAKE',  2850),
      (30, 'ALLEN',  1600),
      (30, 'WARD',   1250);

Example 1: Query the name of the employee with the highest salary

SELECT MAX_BY(ename, sal) FROM dept_sal;

-- The following result is returned:
+------+
| _c0  |
+------+
| KING |
+------+

Example 2: Query the name of the highest-paid employee in each department

SELECT deptno, MAX_BY(ename, sal) AS top_earner
    FROM dept_sal
    GROUP BY deptno;

-- The following result is returned:
+------------+------------+
| deptno     | top_earner |
+------------+------------+
| 10         | KING       |
| 20         | SCOTT      |
| 30         | BLAKE      |
+------------+------------+

Related functions

MAX_BY is an aggregate function. For more information, see Aggregate Functions Overview. For information about other aggregate functions that process multiple input records, such as functions that calculate average values, see Aggregate Functions Overview.

Previous：MAXNext：MEDIAN