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_ID FIRST_NAME LAST_NAME FEES_REQUIRED FEES_PAID ENROLMENT_DATE GENDER
1 John Smith 500 100 01-FEB-2015 M
2 Susan Johnson 150 150 12-JAN-2015 F
3 Tom Capper 350 320 06-MAR-2015 M
4 Mark Holloway 500 410 20-JAN-2015 M
5 Steven Webber 100 80 09-MAR-2015 M
6 Julie Armstrong 100 0 12-FEB-2015 F
7 Michelle Randall 250 23-JAN-2015 F
8 Andrew Cooper 800 400 04-MAR-2015 M
9 Robert Pickering 110 100 30-JAN-2015 M
10 Tanya Hall 150 150 28-JAN-2015 F

 

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_MONTH MIN_FEES
MAR 80
FEB 0
JAN 100

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_MONTH MIN_FEES
MAR 80
JAN 100

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_NAME LAST_NAME ENROLMENT_DATE MIN_TEST
Julie Armstrong 12-FEB-15 0
Tom Capper 06-MAR-15 80
Andrew Cooper 04-MAR-15 80
Tanya Hall 28-JAN-15 100
Mark Holloway 20-JAN-15 100
Susan Johnson 12-JAN-15 100
Robert Pickering 30-JAN-15 100
Michelle Randall 23-JAN-15 100
John Smith 01-FEB-15 0
Steven Webber 09-MAR-15 80

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 Function Cheat Sheet Now: