8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
MIN and MAX Analytic Functions
This article gives an overview of the MIN and MAX analytic functions. If you are new to analytic functions you should probably read this introduction to analytic functions first.
Related articles.
Setup
The examples in this article require the following table.
--DROP TABLE emp PURGE;
CREATE TABLE emp (
empno NUMBER(4) CONSTRAINT pk_emp PRIMARY KEY,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2)
);
INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;
MIN and MAX as Aggregate Functions
The MIN and MAX aggregate functions are used to calculate the minimum and maximum values of a set of data respectively. As aggregate functions they reduce the number of rows, hence the term "aggregate". If the data isn't grouped we turn the 14 rows in the EMP table to a single row with the aggregated values.
SELECT MIN(sal) AS min_sal,
MAX(sal) AS max_sal
FROM emp;
MIN_SAL MAX_SAL
---------- ----------
800 5000
SQL>
We can get more granularity of information by including a GROUP BY clause. In the following example we see the minimum and maximum values on a per-department basis.
SELECT deptno,
MIN(sal) AS min_sal,
MAX(sal) AS max_sal
FROM emp
GROUP BY deptno
ORDER BY deptno;
DEPTNO MIN_SAL MAX_SAL
---------- ---------- ----------
10 1300 5000
20 800 3000
30 950 2850
SQL>
In both cases we have aggregated the data to get the values, returning less rows than we started with. Analytic functions allow us to return these aggregate values while retaining the original row data.
MIN Analytic Function
The basic description for the MIN analytic function is shown below. The analytic clause is described in more detail here.
MIN([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]
Using an empty OVER clause turns the MIN function into an analytic function. The lack of a partitioning clause means the whole result set is treated as a single partition, so we get the minimum salary for all employees, as well as all the original data.
SELECT empno,
ename,
deptno,
sal,
MIN(sal) OVER () AS min_sal
FROM emp
ORDER BY deptno;
EMPNO ENAME DEPTNO SAL MIN_SAL
---------- ---------- ---------- ---------- ----------
7782 CLARK 10 2450 800
7839 KING 10 5000 800
7934 MILLER 10 1300 800
7566 JONES 20 2975 800
7902 FORD 20 3000 800
7876 ADAMS 20 1100 800
7369 SMITH 20 800 800
7788 SCOTT 20 3000 800
7521 WARD 30 1250 800
7844 TURNER 30 1500 800
7499 ALLEN 30 1600 800
7900 JAMES 30 950 800
7698 BLAKE 30 2850 800
7654 MARTIN 30 1250 800
SQL>
Adding the partitioning clause allows us to display the minimum salary per department, along with the employee data for each department.
SELECT empno,
ename,
deptno,
sal,
MIN(sal) OVER (PARTITION BY deptno) AS min_sal_by_dept
FROM emp;
EMPNO ENAME DEPTNO SAL MIN_SAL_BY_DEPT
---------- ---------- ---------- ---------- ---------------
7782 CLARK 10 2450 1300
7839 KING 10 5000 1300
7934 MILLER 10 1300 1300
7566 JONES 20 2975 800
7902 FORD 20 3000 800
7876 ADAMS 20 1100 800
7369 SMITH 20 800 800
7788 SCOTT 20 3000 800
7521 WARD 30 1250 950
7844 TURNER 30 1500 950
7499 ALLEN 30 1600 950
7900 JAMES 30 950 950
7698 BLAKE 30 2850 950
7654 MARTIN 30 1250 950
SQL>
MAX Analytic Function
The basic description for the MAX analytic function is shown below. The analytic clause is described in more detail here.
MAX([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]
Using an empty OVER clause turns the MAX function into an analytic function. The lack of a partitioning clause means the whole result set is treated as a single partition, so we get the maximum salary for all employees, as well as all the original data.
SELECT empno,
ename,
deptno,
sal,
MAX(sal) OVER () AS max_sal
FROM emp;
EMPNO ENAME DEPTNO SAL MAX_SAL
---------- ---------- ---------- ---------- ----------
7369 SMITH 20 800 5000
7499 ALLEN 30 1600 5000
7521 WARD 30 1250 5000
7566 JONES 20 2975 5000
7654 MARTIN 30 1250 5000
7698 BLAKE 30 2850 5000
7782 CLARK 10 2450 5000
7788 SCOTT 20 3000 5000
7839 KING 10 5000 5000
7844 TURNER 30 1500 5000
7876 ADAMS 20 1100 5000
7900 JAMES 30 950 5000
7902 FORD 20 3000 5000
7934 MILLER 10 1300 5000
SQL>
Adding the partitioning clause allows us to display the maximum salary per department, along with the employee data for each department.
SELECT empno,
ename,
deptno,
sal,
MAX(sal) OVER (PARTITION BY deptno) AS max_sal_by_dept
FROM emp;
EMPNO ENAME DEPTNO SAL MAX_SAL_BY_DEPT
---------- ---------- ---------- ---------- ---------------
7782 CLARK 10 2450 5000
7839 KING 10 5000 5000
7934 MILLER 10 1300 5000
7566 JONES 20 2975 3000
7902 FORD 20 3000 3000
7876 ADAMS 20 1100 3000
7369 SMITH 20 800 3000
7788 SCOTT 20 3000 3000
7521 WARD 30 1250 2850
7844 TURNER 30 1500 2850
7499 ALLEN 30 1600 2850
7900 JAMES 30 950 2850
7698 BLAKE 30 2850 2850
7654 MARTIN 30 1250 2850
SQL>
Quick Links
The "*" indicates the function supports the full analytic syntax, including the windowing clause.
For more information see:
Hope this helps. Regards Tim...