Oracle Aggregate Function - Oracle/PLSQL MIN Function

This Oracle tutorial explains how to use the Oracle/PLSQL MIN function.

The Oracle/PLSQL MIN function returns the minimum value of an expression.

MIN function operates on a group of rows and return one row of output.

You can use the MIN function with any valid expression. For example, you can use the MIN() with numbers, strings, and datetimes.

Null values are ignored by MIN. You can use the DISTINCT keyword to exclude duplicate entries.

Syntax

The syntax for the Oracle/PLSQL MIN function is:

SELECT MIN( expression )
 FROM tables
 WHERE conditions;

expression can be a numeric field or formula.

Example - With Single Field

The following SQL returns the minimum salary of all employees.

SELECT MIN(salary) AS "Lowest Salary"
 FROM employees;

Example - Using GROUP BY

The following SQL uses the MIN function to return the name of the department and the minimum salary in the department.

SELECT department, MIN(salary) AS "Lowest salary"
 FROM employees
 GROUP BY department;

Example


CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                      ENAME VARCHAR2(10),
                      HIREDATE DATE);
-- from  w ww.j a  va  2s . com
INSERT INTO EMP VALUES (1, 'SMITH', TO_DATE('17-DEC-1980', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES (2, 'ALLEN', TO_DATE('20-FEB-1981', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES (3, 'WARD',  TO_DATE('22-FEB-1981', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES (4, 'JONES', TO_DATE('2-APR-1981',  'DD-MON-YYYY'));
INSERT INTO EMP VALUES (5, 'MARTIN',TO_DATE('28-SEP-1981', 'DD-MON-YYYY'));


SQL> select min(empno) from emp;

MIN(EMPNO)
----------
         1

SQL>

Using MIN with date value:


SQL> select min(HIREDATE) from emp;

MIN(HIRED
---------
17-DEC-80

SQL>

When MIN() is working on strings, the strings are ordered alphabetically with the minimum string being at the top of the list.


SQL> select min(ename) from emp;

MIN(ENAME)
----------
ALLEN

SQL>

Using the MIN function with the group by clause:


CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                  ENAME VARCHAR2(10),
                  JOB VARCHAR2(9),
                  SAL NUMBER(7, 2),
                  DEPTNO NUMBER(2));
-- from  www  . jav  a2s  .  c  o m
INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK',     800,    20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600,    30);
INSERT INTO EMP VALUES (3, 'WARD',  'SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER',  2975,    20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER',  2850,    30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER',  2850,    10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST',  3000,    20);
INSERT INTO EMP VALUES (9, 'KING',  'PRESIDENT',3000,    10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500,    30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK',    1500,    20);

SQL> select min(sal) from emp group by deptno;

  MIN(SAL)
----------
      1250
       800
      2850

SQL>