📈 Oracle MAX() Function Guide
This tutorial explains how to use the Oracle MAX() function to find the highest value in a column. You'll also learn how to use it with GROUP BY, HAVING, and in subqueries.
📌 What is Oracle MAX()?
The MAX() function is an aggregate function that returns the maximum value from a set of values in a column. It works with numeric, date, and string data types.
🧮 Syntax
MAX(expression)
Where expression is typically a column name. The function ignores NULL values.
📋 Example 1: MAX on a Numeric Column
Find the highest salary in the employees table:
SELECT MAX(salary) AS highest_salary FROM employees;
📋 Example 2: MAX with GROUP BY
Find the highest salary in each department:
SELECT department_id, MAX(salary) AS highest_salary FROM employees GROUP BY department_id;
📋 Example 3: MAX with HAVING
Return only departments where the highest salary is above 15,000:
SELECT department_id, MAX(salary) AS highest_salary FROM employees GROUP BY department_id HAVING MAX(salary) > 15000;
📋 Example 4: MAX in a Subquery
Find employee(s) with the highest salary:
SELECT first_name, last_name, salary FROM employees WHERE salary = ( SELECT MAX(salary) FROM employees );
📋 Example 5: MAX on Date Column
Find the most recent hire date:
SELECT MAX(hire_date) AS latest_hire FROM employees;
📋 Example 6: MAX on Strings
Find the employee with the last name alphabetically (Z → A):
SELECT MAX(last_name) AS last_alphabetically FROM employees;
✅ Summary
MAX()returns the highest value from a set.- Works with numbers, dates, and strings.
- Ignores
NULLvalues. - Use with
GROUP BYandHAVINGfor grouped analysis. - Combine with subqueries to filter specific rows.
The Oracle MAX() function is a simple but powerful tool for extracting top values from your dataset.