MIN is one of the vital Numeric/Math functions of Oracle. It is used to get the minimum of an expression. The MIN function is supported in the various versions of the Oracle/PLSQL, including, Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i and Oracle 8i.
Syntax 1: To calculate simple MIN.
SELECT MIN (aggregate_expression) FROM tables WHERE conditions;
Syntax 2: To calculate MIN and grouping the results by one or more columns.
SELECT expression1, expression2, ... expression_n, MIN(aggregate_expression) FROM tables WHERE conditions GROUP BY expression1, expression2, ... expression_n;
Parameters:
expression1, expression2, … expression_n: It is used to specify the expressions to be included in the GROUP BY clause but is not encapsulated in the MIN function.
aggregate_expression: It is used to specify the column or expression that will be searched.
tables: It is used to specify the tables to retrieve the records from.
conditions: It is an optional parameter which is used to specify the conditions that must be met for selection.
Example 1:
Students Table:
STUDENT_ID STUDENT_NAME STUDENT_AGE 1 Joy 20 2 Smiley 19 3 Happy 30 4 James 45 5 Bond 18
SELECT MIN(student_age) FROM students; |
Output:
18
Explanation:
The MIN of the student_age is 18 and so is the result.
Example 1:
Students Table:
STUDENT_ID STUDENT_NAME STUDENT_AGE 1 Joy 20 2 Smiley 19 3 Happy 30 4 James 45 5 Bond 18
SELECT MIN(student_age) FROM students WHERE student_age > 20; |
Output:
30
Explanation:
The MIN of the student_age such that the student_age is greater than 20 is 30 and so is the result.
Example 3:
SELECT class, MIN(marks) AS "MIN Marks" FROM students WHERE student_id > 10 GROUP BY class; |
Explanation:
Here we will get the name of the class and the minimum marks of the students in the associated class, since we are using the GROUP BY clause. The minimum marks of the students will be calculated only for those students whose student_id is greater than 10.