MAX is an aggregate function that returns the maximum value from a list of values.
Syntax
The syntax of the Oracle MAX function is as follows:
SELECT MAX(expression) FROM table [WHERE condition];
Here,
expression – It could be a list of values or column name from which the highest value is returned.
condition – It is the condition used to further sort the results based on our preference.
The DISTINCT and ALL clause don’t play any role in MAX or MIN functions. The NULL values are ignored by the MAX function.
Examples
For the following examples, we will be using Employee table from HR schema:
1. Simple MAX query
Let’s use the following query to calculate the highest value in salary column of Employees Table:
SELECT MAX( salary ) FROM employees WHERE department_id=100;
Result:
The query returns the maximum salary that an employee has in department_id =100.
2. MAX with Subquery
We will be using the following query to calculate MAX function with Subquery for finding all employees whose salary is greater than the highest salary of employee in department_id =100.
SELECT first_name,last_name, salary, department_id FROM employees WHERE salary >( SELECT MAX( salary ) FROM employees WHERE department_id =100 );
Result:
The query first evaluates the subquery to find the highest value of salary in department_id=100 and then it finds all the employees in the employee table who are having salary higher than that.
3. Oracle MAX with GROUP BY Function
We use the following query for calculating the highest salary in all departments.
SELECT department_id, MAX( salary ) FROM employees GROUP BY department_id ORDER BY department_id;
Result:
The query returns the maximum value of salary in all departments and we use order by to arrange the departments in ascending order.
We can also join Departments table to Employees table to find the name of each department which is present in Departments table. Let’s see this query:
SELECT department_name, MAX( salary ) FROM employees INNER JOIN departments USING(department_id) GROUP BY department_name ORDER BY department_name;
Result:
The query is the same as above, but this time we used inner join to join the Departments table and for finding the department name.
4. Oracle MAX() With HAVING Clause
We use the following query for finding only those departments where the maximum salary is in the range of 5000 to 12000.
For this, we use HAVING clause for adding one more condition to the above query:
SELECT department_name, MAX( salary ) FROM employees INNER JOIN departments USING(department_id) GROUP BY department_name HAVING MAX( salary ) BETWEEN 5000 AND 12500 ORDER BY department_name;
Result:
The query returns only those departments where the maximum salary is in the range of 5000 and 12000.
In this tutorial, we learned how to use MAX function with other functions to return the maximum value in a column of table.