CodesJava

Easy learning with example program codes

MIN function in Oracle

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.

Please follow and like us:
error









Copyright © 2019 CodesJava DMCA.com Protection Status