MAX

Syntax

For information on syntax and semantics, see "Analytic Functions".

Purpose

MAX returns maximum value of expr. You can use it as an aggregate or analytic function.

If you specify DISTINCT, you can specify only the query_partition_clause of the analytic_clause. The ORDER_BY_clause and windowing_clause are not allowed.

See Also: "Aggregate Functions"

Aggregate Example

SELECT MAX(sal) "Maximum" FROM emp;
 
   Maximum
----------
      5000

Analytic Example

The following example calculates, for each employee, the highest salary of the employees reporting to the same manager as the employee.

SELECT mgr, ename, sal, 
   MAX(sal) OVER (PARTITION BY mgr) AS mgr_max
   FROM emp;

      MGR  ENAME      SAL        MGR_MAX
---------- ---------- ---------- ----------
      7566 SCOTT            3000       3000
      7566 FORD             3000       3000
      7698 ALLEN            1600       1600
      7698 WARD             1250       1600
      7698 JAMES             950       1600
      7698 TURNER           1500       1600
      7698 MARTIN           1250       1600
      7782 MILLER           1300       1300
      7788 ADAMS            1100       1100
      7839 JONES            2975       2975
      7839 CLARK            2450       2975
      7839 BLAKE            2850       2975
      7902 SMITH             800        800
           KING             5000       5000

If you enclose this query in the parent query with a predicate, you can determine the employee who makes the highest salary in each department:

SELECT mgr, ename, sal
   FROM (SELECT mgr, ename, sal, 
      MAX(sal) OVER (PARTITION BY mgr) AS rmax_sal
      FROM emp)
      WHERE sal = rmax_sal;

       MGR ENAME             SAL
---------- ---------- ----------
      7566 SCOTT            3000
      7566 FORD             3000
      7698 ALLEN            1600
      7782 MILLER           1300
      7788 ADAMS            1100
      7839 JONES            2975
      7902 SMITH             800
           KING             5000