MAX function in Oracle

MAX is one of the vital Numeric/Math functions of Oracle. It is used to get the maximum of an expression. The MAX 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 MAX.

SELECT MAX (aggregate_expression)
FROM tables
WHERE conditions;

Syntax 2: To calculate MAX and grouping the results by one or more columns.

SELECT expression1, expression2, ... expression_n,
MAX(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 MAX 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 MAX(student_age)
FROM students;

Output:

45

Explanation:
The MAX of the student_age is 45 and so is the result.

Example 2:
Students Table:

STUDENT_ID	STUDENT_NAME	STUDENT_AGE
1	        Joy	        20
2	        Smiley	        19
3	        Happy	        30
4	        James	        45
5	        Bond	        18
SELECT MAX(student_age)
FROM students
WHERE student_age < 40;

Output:

30

Explanation:
The MAX of the student_age such that the student_age is less than 40 is 30 and so is the result.

Example 3:

SELECT class, MAX(marks) AS "MAX Marks"
FROM students
WHERE student_id > 10
GROUP BY class;

Explanation:
Here we will get the name of the class and the maximum marks of the students in the associated class, since we are using the GROUP BY clause. The maximum marks of the students will be calculated only for those students whose student_id is greater than 10.

Please Share