Syntax
MAX([ DISTINCT | ALL ] expr) OVER (analytic_clause)
Purpose
MAX() returns the maximum value in the specified data set.
The argument of MAX() can be a string, in which case the maximum string value is returned.
The
DISTINCTkeyword can be used to retrieve the maximum of the distinct values ofexpr. However, the result is the same as omittingDISTINCT.The
ALLkeyword indicates to search all values ofexpr. By default,ALLis used.
Examples
obclient> CREATE TABLE EXPLOYEES(LAST_NAME CHAR(10), SALARY DECIMAL, JOB_ID CHAR(32));
Query OK, 0 rows affected
obclient> INSERT INTO EXPLOYEES VALUES('JIM', 2000, 'CLEANER');
Query OK, 1 row affected
obclient> INSERT INTO EXPLOYEES VALUES('MIKE', 12000, 'ENGINEERING');
Query OK, 1 row affected
obclient> INSERT INTO EXPLOYEES VALUES('LILY', 13000, 'ENGINEERING');
Query OK, 1 row affected
obclient> INSERT INTO EXPLOYEES VALUES('TOM', 11000, 'ENGINEERING');
Query OK, 1 row affected
obclient> 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
