MIN

The MIN function in Oracle is an aggregate function that returns the smallest (minimum) value from a set of values in a specified column or expression. It can be used on numeric, date, or string data types and is often used to retrieve the earliest date, smallest number, or lexicographically smallest string in a column.

Syntax

MIN(expression)

• expression: The column or value from which the minimum is to be calculated. This expression can be a numeric, date, or string column, or even a derived value (such as a result of a calculation).

Key Points About the MIN Function

1. Aggregate Function:
• The MIN function is an aggregate function. This means it computes a single value from a set of rows. It returns the smallest value of a column or expression across a group of rows.
2. Data Types Supported:
• Numeric Data: The MIN function works with numeric data types, such as NUMBER, FLOAT, DECIMAL, etc.
• Date and Time Data: It can be used to find the earliest date or time in a column of type DATE or TIMESTAMP.
• String Data: When used with string data, it returns the lexicographically smallest string, based on the character set (e.g., ASCII or Unicode).
3. NULL Values:
• The MIN function ignores NULL values. It only considers non-NULL values when determining the minimum value.
4. GROUP BY:
• The MIN function can be used with the GROUP BY clause to find the minimum value for each group of rows.
5. Single Value Output:
• If used without GROUP BY, the MIN function returns the minimum value from the entire table or result set.

Example 1: Using MIN Without GROUP BY

SELECT MIN(salary) AS lowest_salary

FROM employees;

• Explanation: This query retrieves the minimum salary from the entire employees table. The result will be a single value—the smallest salary in the table.

Example 2: Using MIN With GROUP BY

SELECT department_id, MIN(salary) AS lowest_salary

FROM employees

GROUP BY department_id;

• Explanation: This query retrieves the lowest salary within each department. The GROUP BY clause groups the data by department_id, and for each group (department), the MIN function returns the minimum salary.

Example 3: Using MIN With Date Data

SELECT MIN(order_date) AS earliest_order

FROM orders;

• Explanation: This query finds the earliest (oldest) order date in the orders table. The MIN function works with date values and returns the earliest date.

Example 4: Using MIN With String Data

SELECT MIN(employee_name) AS lexicographically_smallest_name

FROM employees;

• Explanation: In this example, the query returns the lexicographically smallest employee name from the employee_name column. The MIN function compares the strings based on their ASCII/Unicode values.

Example 5: Using MIN With a WHERE Clause

SELECT MIN(salary) AS lowest_salary

FROM employees

WHERE department_id = 10;

• Explanation: This query retrieves the minimum salary of employees in department 10. The WHERE clause filters the rows to include only those in department 10, and the MIN function finds the lowest salary among them.

Example 6: Using MIN With HAVING Clause

SELECT department_id, MIN(salary) AS lowest_salary

FROM employees

GROUP BY department_id

HAVING MIN(salary) < 3000;

• Explanation: This query finds the lowest salary in each department, but only returns departments where the minimum salary is less than 3000. The HAVING clause filters the results after the GROUP BY operation.

Example 7: Using MIN With Subqueries

SELECT MIN(salary)

FROM employees

WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Sales');

• Explanation: This query finds the minimum salary of employees working in the 'Sales' department. The subquery finds the department ID for 'Sales', and the outer query uses this ID to find the lowest salary in that department.

Performance Considerations

1. Efficiency:
• The MIN function is generally efficient but can become slower for very large tables if the column being aggregated is not indexed. For example, if you are querying a large table without an index on the column used with MIN, the database may need to perform a full table scan.
2. Indexes:
• If the column being used with MIN is indexed, the query can be significantly faster, especially on large tables, because the database can quickly locate the minimum value.
3. Use in OLAP:
• The MIN function is commonly used in analytical queries to find the smallest value across different dimensions, periods, or categories.

Common Use Cases for MIN Function

1. Find the Minimum Value in a Column:
• This is the most basic use case, where MIN is used to find the smallest number, earliest date, or lexicographically smallest string in a column.
2. Find the Earliest Date:
• The MIN function is often used to retrieve the earliest date, such as the first order placed, the first transaction, or the oldest record in a table.
3. Generate Reports by Groups:
• It can be used in reports to show the lowest value within different groups, such as finding the lowest salary in each department or the lowest sales figure in each region.
4. Identifying Outliers:
• The MIN function can help identify outliers or records that represent the minimum or boundary values in a dataset.

Conclusion

The MIN function in Oracle is a versatile and useful aggregate function that allows you to retrieve the smallest value in a column. It works with numeric, date, and string data types and is commonly used in combination with GROUP BY, HAVING, or subqueries for advanced reporting and analysis. The MIN function is efficient for finding minimum values, but its performance can be affected by the size of the table and the presence of indexes on the relevant columns.