MIN and MAX Analytic
Functions and 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>
