MAX

Syntax

MAX([ DISTINCT | ALL ] expr) OVER (analytic_clause)

Description

Returns the maximum value in the specified data.

The MAX() function can take a string parameter. In such cases, it returns the maximum string value.

• The DISTINCT keyword specifies to find the maximum value among distinct values of expr. The result is the same as if DISTINCT were omitted.

• The ALL keyword specifies to find the maximum value among all values of expr. This is the default.

Examples

CREATE TABLE EXPLOYEES(LAST_NAME CHAR(10), SALARY DECIMAL, JOB_ID CHAR(32));
Query OK, 0 rows affected (0.036 sec)

INSERT INTO EXPLOYEES VALUES('JIM', 2000, 'CLEANER');
Query OK, 1 row affected (0.001 sec)

INSERT INTO EXPLOYEES VALUES('MIKE', 12000, 'ENGINEERING');
Query OK, 1 row affected (0.001 sec)

INSERT INTO EXPLOYEES VALUES('LILY', 13000, 'ENGINEERING');
Query OK, 1 row affected (0.001 sec)

INSERT INTO EXPLOYEES VALUES('TOM', 11000, 'ENGINEERING');
Query OK, 1 row affected (0.001 sec)

SELECT LAST_NAME, MAX(SALARY) OVER (PARTITION BY JOB_ID) MAX_S FROM EXPLOYEES;
+-----------+-------+
| LAST_NAME | MAX_S |
+-----------+-------+
| JIM       |  2000 |
| MIKE      | 13000 |
| LILY      | 13000 |
| TOM       | 13000 |
+-----------+-------+
4 rows in set (0.002 sec)