MAX

Syntax

Description of the illustration max.gif

Purpose

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

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

Aggregate Example

The following example determines the highest salary in the hr.employees table:

SELECT MAX(salary) "Maximum" FROM employees;
 
   Maximum
----------
      24000

Analytic Example

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

SELECT manager_id, last_name, salary, 
   MAX(salary) OVER (PARTITION BY manager_id) AS mgr_max
   FROM employees;

MANAGER_ID LAST_NAME                     SALARY    MGR_MAX
---------- ------------------------- ---------- ----------
       100 Kochhar                        17000      17000
       100 De Haan                        17000      17000
       100 Raphaely                       11000      17000
       100 Kaufling                        7900      17000
       100 Fripp                           8200      17000
       100 Weiss                           8000      17000
. . .

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

SELECT manager_id, last_name, salary
   FROM (SELECT manager_id, last_name, salary, 
      MAX(salary) OVER (PARTITION BY manager_id) AS rmax_sal
      FROM employees) WHERE salary = rmax_sal;

MANAGER_ID LAST_NAME                     SALARY
---------- ------------------------- ----------
       100 Kochhar                        17000
       100 De Haan                        17000
       101 Greenberg                      12000
       101 Higgens                        12000
       102 Hunold                          9000
       103 Ernst                           6000
       108 Faviet                          9000
       114 Khoo                            3100
       120 Nayer                           3200
       120 Taylor                          3200
       121 Sarchand                        4200
       122 Chung                           3800
       123 Bell                            4000
       124 Rajs                            3500
       145 Tucker                         10000
       146 King                           10000
       147 Vishney                        10500
       148 Ozer                           11500
       149 Abel                           11000
       201 Goyal                           6000
       205 Gietz                           8300
           King                           24000