×
☰ See All Chapters

Oracle MIN Function

Oracle MIN function returns the lowest value in the specified column. Specified column should have numeric data type; MIN function does not accept columns having a data type other than numeric, such as character or date. NULL values are ignored when using the MIN function. The DISTINCT command is an option. However, because the minimum value for all the rows is the same as the distinct minimum value, DISTINCT is useless.

The syntax of the MIN function is as follows:

SELECT MIN(<column-name>) FROM TABLE_NAME;

Oracle MIN Function Examples

Creating table for demonstrating MIN function

CREATE TABLE EMP_SALARY_COMMISION

(EMP_ID NUMBER(4) ,

SALARY NUMBER(7,2),

COMM NUMBER(7,2)

);

 

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY, COMM) VALUES ('100', '1000', '110');

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY, COMM) VALUES ('101', '1000', '100');

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY) VALUES ('102', '1000');

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY, COMM) VALUES ('103', '1000', '20');

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY, COMM) VALUES ('104', '2000', '100');

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY) VALUES ('105', '2000');

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY) VALUES ('106', '2000');

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY, COMM) VALUES ('107', '3000', '60');

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY, COMM) VALUES ('108', '3000', '60');

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY) VALUES ('109', '3000');

 

COMMIT;

oracle-min-function-0

MIN of all rows

SELECT MIN(COMM) FROM EMP_SALARY_COMMISION; --------- 20

MIN of distinct rows

SELECT MIN(DISTINCT COMM) FROM EMP_SALARY_COMMISION; --------- 20

Adding alias to MIN value

SELECT MIN(SALARY) AS MIN_SALARY FROM EMP_SALARY_COMMISION;

 

oracle-min-function-1

All Chapters
Author