MIN

Syntax

For information on syntax and semantics, see "Analytic Functions".

Purpose

MIN returns minimum 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.

See Also: "Aggregate Functions"

Aggregate Example

SELECT MIN(hiredate) "Earliest" FROM emp;
 
Earliest
---------
17-DEC-80

Analytic Example

The following example determines, for each employee, the employees who were hired on or before the same date as the employee. It then determines the subset of employees reporting to the same manager as the employee, and returns the lowest salary in that subset.

SELECT mgr, ename, hiredate, sal,
   MIN(sal) OVER(PARTITION BY mgr ORDER BY hiredate
   RANGE UNBOUNDED PRECEDING) as p_cmin
   FROM emp;

       MGR ENAME      HIREDATE         SAL     P_CMIN
---------- ---------- --------- ---------- ----------
      7566 FORD       03-DEC-81       3000       3000
      7566 SCOTT      19-APR-87       3000       3000
      7698 ALLEN      20-FEB-81       1600       1600
      7698 WARD       22-FEB-81       1250       1250
      7698 TURNER     08-SEP-81       1500       1250
      7698 MARTIN     28-SEP-81       1250       1250
      7698 JAMES      03-DEC-81        950        950
      7782 MILLER     23-JAN-82       1300       1300
      7788 ADAMS      23-MAY-87       1100       1100
      7839 JONES      02-APR-81       2975       2975
      7839 BLAKE      01-MAY-81       2850       2850
      7839 CLARK      09-JUN-81       2450       2450
      7902 SMITH      17-DEC-80        800        800
           KING       17-NOV-81       5000       5000