Purpose
You can call this function to return the maximum value in the specified column. You can use it as an aggregate or analytic function.
Note
- When you use it as an analytic function, use the
OVERclause to define a window over the data on which the function operates. The function operates on a group of rows to return a list of values.- When you use it as an aggregate function, the function operates on a set of rows and returns a single value. You do not need to add the
OVERclause.
Syntax
MAX ([ DISTINCT UNIQUE ALL ] expr) [ OVER (analytic_clause) ]
Parameters
| Parameter | Description |
|---|---|
| DISTINCT UNIQUE ALL | (Optional) Specifies whether to remove duplicates during the query. Default value: ALL.
|
| expr | An expression or column of the numeric, character, date, or other types. |
| OVER | You can use the OVER clause to define a window over the data on which the function operates. For more information about analytic functions, see Analytic functions. |
Return types
This function returns the same value type as the expr parameter.
Examples
Assume that you have created the table employees.
obclient> SELECT * FROM employees;
+---------------+------------+-----------+------------+--------+
DEPARTMENT_ID MANAGER_ID LAST_NAME HIREDATE SALARY
+---------------+------------+-----------+------------+--------+
30 100 Raphaely 2017-07-01 1700
30 100 De Haan 2018-05-01 11000
40 100 Errazuriz 2017-07-21 1400
50 100 Hartstein 2019-10-05 14000
50 100 Raphaely 2017-07-22 1700
50 100 Weiss 2019-10-05 13500
90 100 Russell 2019-07-11 13000
90 100 Partners 2018-12-01 14000
+---------------+------------+-----------+------------+--------+
8 rows in set
Aggregate example
Query for the maximum value in the salary column.
obclient> SELECT MAX(salary) FROM employees;
+-------------+
MAX(SALARY)
+-------------+
14000
+-------------+
1 row in set
Analytic example
Group the data records in the table by the department_id column. Return the maximum value in the salary column.
obclient> SELECT department_id,last_name,salary,
MAX(salary) OVER (PARTITION BY department_id) AS rmax_sal
FROM employees;
+---------------+-----------+--------+----------+
DEPARTMENT_ID LAST_NAME SALARY RMAX_SAL
+---------------+-----------+--------+----------+
30 Raphaely 1700 11000
30 De Haan 11000 11000
40 Errazuriz 1400 1400
50 Hartstein 14000 14000
50 Raphaely 1700 14000
50 Weiss 13500 14000
90 Russell 13000 14000
90 Partners 14000 14000
+---------------+-----------+--------+----------+
8 rows in set