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.