MIN Function in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

MIN Function in Oracle with Examples

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

MIN() Function in Oracle

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

SELECT MIN(columnname) FROM tablename;

Examples to Understand MIN Function in Oracle:

We are going to use the following Employee table to understand the need and use of MIN 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');

MIN() Function in Oracle with Numeric Data Type

Our requirement is to find the MINIMUM age from the Employee table. To do so, we need to pass the Age column to the MIN function as shown in the below SQL query.

SELECT MIN(Age) AS Min_AGE FROM Employee;

When you execute the above SELECT Statement, you will get the following output. Here, we get the smallest value in the age column of the Employee table i.e. 25.

MIN function with GROUP by Clause in Oracle

Now we need to find the minimum salary in each department. To do so, we need to pass the salary column to the MIN 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, MIN(Salary) Min_Salary
FROM Employee
GROUP BY Department;

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

MIN Function with WHERE Clause in Oracle

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

SELECT MIN(Salary) Min_Salary
FROM Employee
WHERE Gender = 'Male';

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

MIN Function with String Values in Oracle

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

SELECT MIN(Name) AS Min_Name FROM Employee;

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

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