FB

Oracle MIN FunctionThe MIN function is one that I use quite a lot in Oracle SQL. In this article, I’ll explain how to use it, and show you some examples.

Purpose of the Oracle MIN Function

The MIN function returns the minimum value of the expression you provide it. This is almost the same as the lowest value.

Just like the MAX function, it’s a standard SQL function, so it works in many versions of SQL. I’ll be looking at the Oracle version of MIN.

 

Syntax

Similar to the MAX function and other functions, you can use the MIN function as an aggregate function or an analytic function.

The syntax of MIN as an aggregate function is:

MIN ( [DISTINCT/ALL] expr )

The syntax of MIN as an analytic function is:

MIN ( [DISTINCT/ALL] expr ) OVER ( analytic_clause )

This is good to know, as you might find cases where you need to use the analytic version. However, almost every time I’ve used the MIN function has been as an aggregate function.

 

Parameters

The parameters of the MIN function are:

  • expr (mandatory): This is the expression that is being considered for the MIN function. It can be a value or set of values, a column name, or any other valid Oracle expression.
  • analytic_clause (optional): This is used when you’re using MIN as an analytic function. It works similar to grouping data for your MIN function, but a bit different. See the examples below for more information.

 

How Can I Find the Minimum of Two Values in Oracle?

You can use the MIN function for this.

You can pass the two values as parameters to the MIN function.

SELECT MIN (value1, value2) FROM table;

See the examples section below for more information.

Can I Use the Oracle MIN Function in the WHERE Clause?

No, you can’t. This is because the WHERE clause is run before the aggregate function.

You would need to use it in a HAVING clause, or use a subquery.

See the examples section below for more information.

 

Can I Use Oracle MIN with Dates?

Yes, you can. MIN will return the earliest date in the supplied list of dates.

 

What Does The “Oracle MIN Over” Do?

Using the OVER keyword on a function allows you to write it as an analytic function. This means you can view the MIN value in a different way.

This OVER clause lets you specify what you want the MIN function to be grouped by, or what the MIN function is contained to.

Normally, the MIN function gets the minimum value of everything. You can use OVER to specify that you want the minimum of a certain group of fields (e.g. MIN hiredate in a department).

We can see this in action in the examples section below.

 

Examples of the MIN Function

Here are some examples of the MIN function. I find that examples are the best way for me to learn about code, even with the explanation above.

First, let’s look at our student table.

SELECT * FROM student;

Results:

STUDENT_IDFIRST_NAMELAST_NAMEFEES_REQUIREDFEES_PAIDENROLMENT_DATEGENDER
1JohnSmith50010001-FEB-2015M
2SusanJohnson15015012-JAN-2015F
3TomCapper35032006-MAR-2015M
4MarkHolloway50041020-JAN-2015M
5StevenWebber1008009-MAR-2015M
6JulieArmstrong100012-FEB-2015F
7MichelleRandall25023-JAN-2015F
8AndrewCooper80040004-MAR-2015M
9RobertPickering11010030-JAN-2015M
10TanyaHall15015028-JAN-2015F

 

Example 1

This example shows a simple use of the MIN function on a number field.

SELECT MIN(fees_paid) AS MIN_TEST
FROM student ;

Result:

MIN_TEST
0

It shows the value of 0 as that is the lowest value in the fees_paid column.

 

Example 2

This example shows how MIN would work on a text field.

SELECT MIN(last_name) AS MIN_TEST
FROM student;

Result:

MIN_TEST
Armstrong

This shows Armstrong because, alphabetically, it is the first value that is returned. When using MIN on text values, it finds the first value when sorting alphabetically.

 

Example 3

This example shows how MIN would work on a date field.

SELECT MIN(enrolment_date) AS MIN_TEST
FROM student;

Result:

MIN_TEST
12-JAN-2015

This shows the first enrolment_date in the table.

 

Example 4

This example uses the GROUP BY clause with MIN. Because MIN is an aggregate function, you need to group all non-aggregated fields using the GROUP BY clause.

SELECT TO_CHAR(enrolment_date, 'MON') AS ENROLMENT_MONTH,
MIN(fees_paid) AS MIN_FEES
FROM student
GROUP BY TO_CHAR(enrolment_date, 'MON');

Result:

ENROLMENT_MONTHMIN_FEES
MAR80
FEB0
JAN100

This shows the months of enrolment, along with the minimum of fees paid for each month.

 

Example 5

This example uses the MIN function in a HAVING clause.

SELECT TO_CHAR(enrolment_date, 'MON') AS ENROLMENT_MONTH,
MIN(fees_paid) AS MIN_FEES
FROM student
GROUP BY TO_CHAR(enrolment_date, 'MON')
HAVING MIN(fees_paid) > 50;

Result:

ENROLMENT_MONTHMIN_FEES
MAR80
JAN100

This shows the months where the MIN_FEES for the month is greater than 50.

 

Example 6

This example uses the MIN function as an analytic function. I’ve used the OVER clause to get the MIN of fees_paid for each of the enrolment months.

SELECT first_name,
last_name,
enrolment_date,
MIN(fees_paid) OVER (PARTITION BY TO_CHAR(enrolment_date, 'MON'))  AS MIN_TEST
FROM student
ORDER BY last_name, first_name;

Result:

FIRST_NAMELAST_NAMEENROLMENT_DATEMIN_TEST
JulieArmstrong12-FEB-150
TomCapper06-MAR-1580
AndrewCooper04-MAR-1580
TanyaHall28-JAN-15100
MarkHolloway20-JAN-15100
SusanJohnson12-JAN-15100
RobertPickering30-JAN-15100
MichelleRandall23-JAN-15100
JohnSmith01-FEB-150
StevenWebber09-MAR-1580

This table shows all of the employees, as well as the MIN of the fees_paid for each month of enrolment. Unlike using a GROUP BY, this method shows all student records.

 

Similar Functions

Some functions which are similar to the MIN function are:

  • MAX – This function gets the highest of the supplied values. It’s the opposite of the MIN function.
  • AVG – This function gets the average of the specified values.
  • SUM – This function adds up all of the values supplied to it.
  • COUNT – This function counts the number of values supplied.

You can find a full list of Oracle SQL functions here.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Get Your SQL Cheat Sheets Now: