ORACLE MIN

MIN is an aggregate group function that returns the minimum value from a set of values.

Syntax

The syntax of the Oracle MAX function is as follows:

SELECT MIN(expression)
FROM table
[WHERE condition];

Here,

expression – It could be a list of values or column name from which the lowest 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 default by the MIN function.

Examples

For the following examples, we will be using the Employees table from the HR schema

1. Simple MIN Query

We use the following query for calculating the lowest value of salary in department_id =60.

SELECT
MIN( salary )
FROM
employees
WHERE department_id=60;

Result:

The query returns the minimum value of salary in department_id=60.

2. Oracle MIN with Subquery

If we want to find all the employees who have salary lower than the lowest value of salary in department_id =60, then we use this query:

SELECT first_name,last_name, salary, department_id
FROM employees
WHERE salary <(
SELECT MIN( salary )
FROM employees
WHERE department_id =60);

Result:

MIN Oracle Function Example with Subquery

The query returns all the employees in Employee table where the salary of employees is lower than the lowest value of salary of an employee in department_id =60.

3. Oracle MIN with Group by clause

If we want to find the minimum value of salary in each department, then we use this query:

SELECT department_id, MIN( salary )
FROM employees
GROUP BY
department_id
ORDER BY
department_id;

Result:

MIN Oracle Function Example with Group By

The query returns the lowest values of salary in each department as we grouped the minimum salaries with department.

Suppose, we want to find the name of each department and we know that department names are not present in employee table but we have a column named Department Name in Departments table.

Then we use the following query to join the employee and departments table and for displaying the departments name instead of department_id in above query:

SELECT department_name, MIN( salary )
FROM employees
INNER JOIN departments USING(department_id)
GROUP BY
department_name
ORDER BY
department_name;

Result:

MIN Oracle Function Example with INner Join

The query shows us the minimum value of salary in each department and also the department name.

4. MIN With HAVING Clause

We use the following query for finding all the departments where the minimum salary in each department is in the range of 1000 and 5000.

SELECT department_name, MIN( salary )
FROM employees
INNER JOIN departments USING(department_id)
GROUP BY
department_name
HAVING
MIN( salary ) BETWEEN 1000 AND 5000
ORDER BY
department_name;

Result: