Skip Headers
Oracle® Database SQL Language Reference
11g Release 1 (11.1)

Part Number B28286-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

MIN

Syntax

Description of min.gif follows
Description of the illustration min.gif

See Also:

"Analytic Functions" for information on syntax, semantics, and restrictions

Purpose

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

See Also:

"About SQL Expressions" for information on valid forms of expr, "Floating-Point Numbers" for information on binary-float comparison semantics, and "Aggregate Functions"

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
. . .