Syntax
MAX([ DISTINCT ALL ] expr) OVER (analytic_clause)
Purpose
You can call this function to return 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 (0.08 sec)
obclient> INSERT INTO EXPLOYEES VALUES('JIM', 2000, 'CLEANER');
Query OK, 1 row affected (0.11 sec)
obclient> INSERT INTO EXPLOYEES VALUES('MIKE', 12000, 'ENGINEERING');
Query OK, 1 row affected (0.00 sec)
obclient> INSERT INTO EXPLOYEES VALUES('LILY', 13000, 'ENGINEERING');
Query OK, 1 row affected (0.00 sec)
obclient> INSERT INTO EXPLOYEES VALUES('TOM', 11000, 'ENGINEERING');
Query OK, 1 row affected (0.01 sec)
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 (0.01 sec)