Oracle8i SQL Reference Release 2 (8.1.6) A76989-01 |
|
Functions, 50 of 121
For information on syntax and semantics, see "Analytic Functions".
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.
SELECT MAX(sal) "Maximum" FROM emp; Maximum ---------- 5000
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
|
![]() Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|