MIN

Syntax

Purpose

MIN returns minimum value of expr. You can use it as an aggregate or analytic function.

Aggregate Example

The following statement returns the earliest hire date in the hr.employees table:

SELECT MIN(hire_date) "Earliest" FROM employees;
 
Earliest
---------
17-JUN-87

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 manager_id, last_name, hire_date, salary,
   MIN(salary) OVER(PARTITION BY manager_id ORDER BY hire_date
   RANGE UNBOUNDED PRECEDING) AS p_cmin
   FROM employees
   ORDER BY manager_id, last_name, hire_date, salary, p_cmin;

MANAGER_ID LAST_NAME                 HIRE_DATE     SALARY     P_CMIN
---------- ------------------------- --------- ---------- ----------
       100 Cambrault                 15-OCT-99      11000       6500
       100 De Haan                   13-JAN-93      17000      17000
       100 Errazuriz                 10-MAR-97      12000       7900
       100 Fripp                     10-APR-97       8200       7900
       100 Hartstein                 17-FEB-96      13000       7900
       100 Kaufling                  01-MAY-95       7900       7900
       100 Kochhar                   21-SEP-89      17000      17000
       100 Mourgos                   16-NOV-99       5800       5800
       100 Partners                  05-JAN-97      13500       7900
       100 Raphaely                  07-DEC-94      11000      11000
       100 Russell                   01-OCT-96      14000       7900
. . .