Term: MAX

Definition:
The Oracle PL/SQL MAX function is used to get the maximum value of a column. Like the MIN function, it can be used for both aggregate and analytic purposes.

Example Syntax:

MAX(expression) OVER (analytic clause)

Example Usage:

The SQL statement below retrieves maximum employee id (EMPNO) from the EMPLOYEE table.

SELECT MAX(EMPNO) FROM EMPLOYEE;

The SQL statement below retrieves the maximum salary in each department from EMPLOYEE table. This demonstrates the aggregate behavior of MAX.

SELECT DEPTNO, MAX(SALARY)

FROM EMPLOYEE

GROUP_BY DEPTNO

The SQL statement below also retrieves the maximum salary per department from EMPLOYEE table, but without using GROUP_BY clause. This shows the analytic behavior of MAX.

SELECT DISTINCT DEPTNO, MAX(SALARY) OVER (PARTITION BY DEPTNO)

FROM EMPLOYEE

Related Links:

Related Code Snippets:

MAX - Returns the maximum value by partition.
Date Functions: MAX - Returns the latest date.
SEQUENCE: Find sequence MAX value - Here's how to find the MAX value in a sequence.