MAX

Syntax

Purpose

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

Aggregate Example

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

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

Analytic Examples

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
 ORDER BY manager_id, last_name, salary;
MANAGER_ID LAST_NAME SALARY MGR_MAX
---------- ------------------------- ---------- ----------
 100 Cambrault 11000 17000
 100 De Haan 17000 17000
 100 Errazuriz 12000 17000
 100 Fripp 8200 17000
 100 Hartstein 13000 17000
 100 Kaufling 7900 17000
 100 Kochhar 17000 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
 ORDER BY manager_id, last_name, salary;
MANAGER_ID LAST_NAME SALARY
---------- ------------------------- ----------
 100 De Haan 17000
 100 Kochhar 17000
 101 Greenberg 12008
 101 Higgins 12008
 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 Fay 6000
 205 Gietz 8300
 King 24000
 
22 rows selected.