MAX Function in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

MAX Function in Oracle with Examples

In this article, I am going to discuss MAX Function in Oracle with Examples. Please read our previous article where we discussed MIN Function in Oracle with Examples.

MAX() Function in Oracle

The MAX function in Oracle is used to return the largest value of the given column. The MAX function works on numeric columns as well as string columns. The following SQL statement shows how to use the MAX function in Oracle.

SELECT MAX(columnname) FROM tablename;

Examples to Understand MAX Function in Oracle:

We are going to use the following Employee table to understand the need and use of MAX Function in Oracle with Examples.

Please execute the below SQL query to drop the existing Employee table and create a new Employee table with the required sample data.

DROP Table Employee;

CREATE TABLE Employee (
  Id INT PRIMARY KEY,
  Name VARCHAR(15),
  Department VARCHAR(10),
  Salary NUMBER(8, 2),
  Gender VARCHAR(10),
  Age INT,
  City VARCHAR(10)
);

INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1001, 'John', 'IT', 35000, 'Male', 25, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1002, 'Smith', 'HR', 45000, 'Female', 27, 'Mumbai');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1003, 'James', 'Finance', 50000, 'Male', 28, 'Delhi');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1004, 'Mike', 'Finance', 50000, 'Male', 28, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1005, 'Linda', 'HR', 75000, 'Female', 26, 'Mumbai');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1006, 'Anurag', 'IT', 35000, 'Male', 25, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1007, 'Priyanla', 'HR', 45000, 'Female', 27, 'Mumbai');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1008, 'Sambit', 'IT', 50000, 'Male', 28, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1009, 'Pranaya', 'IT', 50000, 'Male', 28, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1010, 'Hina', 'HR', 75000, 'Female', 26, 'Mumbai');

MAX() Function in Oracle with Numeric Data Type

Our requirement is to find the Maximum Salary from the Employee table. To do so, we need to pass the Salary column to the MAX function as shown in the below SQL query.

SELECT MAX(Salary) AS Max_Salary FROM Employee;

When you execute the above SELECT Statement, you will get the following output. Here, we get the largest value in the Salary column of the Employee table i.e. 75000.

MAX function with GROUP by Clause in Oracle

Now we need to find the maximum salary in each department. To do so, we need to pass the salary column to the MAX function as well as we need to group the employees by the department by using the GROUP by clause as shown in the below query.

SELECT Department, MAX(Salary) Max_Salary
FROM Employee
GROUP BY Department;

When you execute the above SELECT query, you will get the following output.

MAX Function with WHERE Clause in Oracle

Now we need to find the largest salary among all the Male Employees. To do so, we need to pass the Salary column to the MAX aggregate function and we also need to filter the Male employees by using the WHERE clause as shown in the below query.

SELECT MAX(Salary) Max_Salary
FROM Employee
WHERE Gender = 'Male';

When you execute the above SELECT query, you will get the following output.

MAX Function with String Values in Oracle

It is also possible in Oracle to use the MAX function on the string values like the below example.

SELECT MAX(Name) AS Max_Name FROM Employee;

When you execute the above SQL query, you will get the following output. Here, we get the largest value that is alphabetically the largest value in the name column. For string column values, the MAX function arranges the values alphabetically and gets the first descending value from the column.

In the next article, I am going to discuss AVG Function in Oracle with Examples. Here, in this article, I try to explain MAX Function in Oracle with Examples and I hope you enjoy this MAX Function in Oracle with Examples article.