Oracle MIN Function

MIN returns the smallest value in a group. It works on any data type that can be sorted: the lowest salary, the earliest date, the first name in alphabetical order, the shortest interval. Combined with KEEP, it can also return a value from the row that ranks first by another column.

Code for This Guide

The main examples are in the examples/aggregate-functions folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

Syntax:

min([distinct | all] expr)

MIN ignores NULLs and returns NULL for an empty group. DISTINCT is allowed but changes nothing, since the minimum of the distinct values is the same.

The Earliest Hire Date

The first query finds the earliest hire date with MIN, alongside other aggregates.

Example:

select count(*)                     as employees,
       count(commission_pct)        as with_commission,
       count(distinct department_id) as departments,
       sum(salary)                  as payroll,
       round(avg(salary), 2)        as avg_salary,
       min(hire_date)               as first_hire,
       max(hire_date)               as last_hire
from   employees;

select department_id, count(*) as staff, sum(salary) as payroll, max(salary) as top_salary
from   employees
group  by department_id
order  by department_id
fetch  first 4 rows only;

Output:

   EMPLOYEES    WITH_COMMISSION    DEPARTMENTS    PAYROLL    AVG_SALARY FIRST_HIRE     LAST_HIRE
____________ __________________ ______________ __________ _____________ ______________ ______________
          55                  7             10     803400      14607.27 01-MAR-2012    02-FEB-2026

   DEPARTMENT_ID    STAFF    PAYROLL    TOP_SALARY
________________ ________ __________ _____________
              10        2      84000         48000
              20       12     213800         27000
              30       11      99100         22000
              40       10     142100         34000

Text, Dates, and Numbers

MIN applies the ordering of each data type: alphabetical for text, chronological for dates, numeric for numbers.

Example:

select min(last_name) as first_name_alpha, min(hire_date) as earliest_hire,
       min(salary) as lowest_salary, min(elevation_ft) as lowest_airport
from   employees cross join (select min(elevation_ft) as elevation_ft from airports);

Output:

FIRST_NAME_ALPHA    EARLIEST_HIRE       LOWEST_SALARY    LOWEST_AIRPORT
___________________ ________________ ________________ _________________
Al Mansoori         01-MAR-2012                  4500               -11

Al Mansoori comes first alphabetically, 1 March 2012 is the earliest hire, and the lowest airport lies 11 feet below sea level. Text compares by the session's sort order, which is binary by default, so uppercase letters come before lowercase ones.

Things to Know

• To get another column from the row with the minimum, use KEEP (DENSE_RANK FIRST ORDER BY ...) or ROW_NUMBER, not MIN on each column separately.
• MIN finds the smallest value down a column; LEAST finds the smallest among several values of one row.
• An index on the column lets Oracle find MIN without reading the whole table.

Related Guides

• Oracle NLSSORT Function

Conclusion

MIN returns the smallest non-NULL value of a group for numbers, text, dates, and intervals. Use KEEP or ROW_NUMBER to fetch other columns of that row, and LEAST to compare values within a row.