Declaration
MAX([ DISTINCT | ALL ] expr) OVER (analytic_clause)
Description
Returns the maximum value in the specified data.
The MAX() function can also accept a string parameter, in which case it returns the maximum string value.
The
DISTINCTkeyword indicates to find the maximum value of distinctexprvalues. The result is the same as omittingDISTINCT.The
ALLkeyword indicates to find all values ofexpr. The default isALL.
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