ORACLE MAX

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];

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;

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
);

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;

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;

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;

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.