FB

The SQL MIN function is used to find the lowest or minimum value. Learn how to use it and see some examples in this article.

 

What is the SQL 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 (Oracle, SQL Server, MySQL, PostgreSQL, and more)

 

SQL 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] expression )

The syntax of MIN as an analytic function is:

MIN ( [DISTINCT/ALL] expression ) 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:

  • expression (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 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.

 

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

 

Example 1 – Simple MIN

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

SELECT MIN(fees_paid)
FROM student ;

Result:

MIN(FEES_PAID)
0

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

 

Example 2 – Simple MIN with Alias

This example shows a simple use of the MIN function on a number field. We’ve used an alias here on the column to make it easier to read and work with

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 3 – MIN with Text

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 4 – MIN with Date

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-2021

This shows the first enrolment_date in the table.

 

Example 5 – MIN with GROUP BY

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 6 – MIN with HAVING

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 7 – MIN as Analytic Function

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

 

How Can I Find the Minimum of Two Values?

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 for more information.

Can I Use the 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 for more information.

 

Can I Use SQL MIN with Dates?

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

 

What Does The “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.

 

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. Other function lists are coming soon.

Share via
Copy link
Powered by Social Snap