MAX - MaxCompute - Alibaba Cloud Documentation Center

Calculates the maximum value of a column or the maximum value of expr in a window.

Limits

Before you use window functions, take note of the following limits:

Window functions are supported only in SELECT statements.
A window function cannot contain nested window functions or nested aggregate functions.
You cannot use window functions together with aggregate functions of the same level.

Syntax

-- Calculate the maximum value of a column.
max(<colname>)

-- Calculate the maximum value of expr in a window.
max(<expr>) over([partition_clause] [orderby_clause] [frame_clause])

Description

Calculates the maximum value of a column or the maximum value of expr in a window.

Parameters

colname: required. The name of a column, which can be of any data type other than BOOLEAN.
expr: required. The expression that is used to calculate the maximum value. The input value can be of any data type other than BOOLEAN. If the input value for a row is NULL, this row is not used for calculation.
partition_clause, orderby_clause, and frame_clause: For more information about these parameters, see windowing_definition.

Return value

The data type of the return value is the same as the data type of the colname parameter. The return value varies based on the following rules:
- If the value of colname is null, the row that contains this value is not used for calculation.
- If the value of colname is of the BOOLEAN type, the value is not used for calculation.
A value of the same type as expr is returned.

Sample data

This section provides sample source data and examples for you to understand how to use the functions. Create a table named emp and insert the sample data into the table. Sample statement:

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 sample 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: Use the deptno column to define a window and obtain the maximum value of the sal column. The ORDER BY clause is not specified. This function returns the maximum value of the current window. The current window includes the rows that have the same deptno value. Sample statement:

select deptno, sal, max(sal) over (partition by deptno) from emp;

The following result is returned:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 5000       |   -- This row is the first row of this window. The return value is the maximum value among the values from the first row to the sixth row. 
| 10         | 2450       | 5000       |   -- The return value is the maximum value among the values from the first row to the sixth row. 
| 10         | 5000       | 5000       |   -- The return value is the maximum value among the values from the first row to the sixth row. 
| 10         | 1300       | 5000       |
| 10         | 5000       | 5000       |
| 10         | 2450       | 5000       |
| 20         | 3000       | 3000       |
| 20         | 3000       | 3000       |
| 20         | 800        | 3000       |
| 20         | 1100       | 3000       |
| 20         | 2975       | 3000       |
| 30         | 1500       | 2850       |
| 30         | 950        | 2850       |
| 30         | 1600       | 2850       |
| 30         | 1250       | 2850       |
| 30         | 1250       | 2850       |
| 30         | 2850       | 2850       |
+------------+------------+------------+

Example 2: Use the deptno column to define a window and obtain the maximum value of the sal column. The ORDER BY clause is specified. This function returns the maximum value among the values from the first row to the current row in the current window. The current window includes the rows that have the same deptno value. Sample statement:

select deptno, sal, max(sal) over (partition by deptno order by sal) from emp;

The following result is returned:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 1300       |   -- This row is the first row of this window. 
| 10         | 1300       | 1300       |   -- The return value is the maximum value among the values in the first and second rows. 
| 10         | 2450       | 2450       |   -- The return value is the maximum value among the values from the first row to the third row. 
| 10         | 2450       | 2450       |   -- The return value is the maximum value among the values from the first row to the fourth row. 
| 10         | 5000       | 5000       |
| 10         | 5000       | 5000       |
| 20         | 800        | 800        |
| 20         | 1100       | 1100       |
| 20         | 2975       | 2975       |
| 20         | 3000       | 3000       |
| 20         | 3000       | 3000       |
| 30         | 950        | 950        |
| 30         | 1250       | 1250       |
| 30         | 1250       | 1250       |
| 30         | 1500       | 1500       |
| 30         | 1600       | 1600       |
| 30         | 2850       | 2850       |
+------------+------------+------------+

Example 3: Calculate the highest salary (sal) of all employees. Sample statement:
```
select max(sal) from emp;
```
The following result is returned:
```
+------------+
| _c0        |
+------------+
| 5000       |
+------------+
```

Example 4: Use this function with GROUP BY to group all employees by department (deptno) and calculate the highest salary (sal) of employees in each department. Sample statement:

select deptno, max(sal) from emp group by deptno;

The following result is returned:

+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | 5000       |
| 20         | 3000       |
| 30         | 2850       |
+------------+------------+

Related functions

MAX is an aggregate function or a window function.

For more information about the functions that are used to calculate the average value of multiple input records and to aggregate parameters, see Aggregate functions.
For more information about the functions that are used to calculate the sum of data of columns in a window and to sort data, see Window functions.