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 window 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.