The SQL MIN function is an aggregate function that is used to find the minimum values in columns or rows in a table.
In this article, you will see how to use the MIN function in SQL Server. You will see the different scenarios where you can use the SQL MIN function to get desired results. So, let’s begin without any ado.
SQL MIN Syntax
The syntax of the MIN function is very straightforward. You need to pass the column in which you want to find the minimum value as shown below:
|
1 2 3 |
MIN(column_name) |
Basic Examples of SQL MIN Function
Let’s see some basic examples of the SQL MIN function.
Execute the following script to create a table named Scores which contains two columns: “StudentA” and “StudentB”. This table will be used to calculate minimum values in this section.
|
1 2 3 4 |
CREATE TABLE Scores(StudentA float, StudentB float) INSERT INTO Scores values(15,26),(10,15),(20,19),(40,35),(25,47) |
The following SELECT query shows all the columns in the table:
|
1 2 3 |
SELECT * FROM Scores |
Let’s use the MIN function to find the minimum value in the “StudentA” column:
|
1 2 3 4 |
SELECT MIN(StudentA) as MinA FROM Scores |
Output:
You can see the minimum value i.e. 10 in the output.
The SQL MIN function can also be used in conjunction with the SELECT statement to return the minimum values from two or more columns. For instance, the script below returns minimum values from both the “StudentA” and “StudentB” columns.
|
1 2 3 4 |
SELECT MIN(StudentA) as MinA, MIN(StudentB) as MinB FROM Scores |
Output:
In the above output, you can see the minimum values from both the “StudentA” and “StudentB” columns.
You can also use the WHERE clause in conjunction with the MIN function in order to filter your results. The following script returns minimum values from both the columns in the Scores table where the value in the “StudentA” column is greater than 20.
|
1 2 3 4 5 |
SELECT MIN(StudentA) as MinA, MIN(StudentB) as MinB FROM Scores WHERE StudentA > 20 |
Output:
The output shows that the minimum value returned from the student “StudentA” column which is greater than 20 is 25. For the column “StudentB” the minimum value returned is 35.
This might look strange at first because the “StudentB” column contains a value of 26 which is smaller than 35, yet 35 is returned as the minimum value from the “StudentB” column. The reason is that the value for the “StudentA” column is 15 for the row where the “StudentB” column contains 26 and since the WHERE clause removes all the rows where the “StudentA” column contains values smaller than 20, the row where “StudentB” column contains 26 is also removed.
To find the minimum value in both columns, you can use two SELECT queries and then use the UNION operator to concatenate the results as shown below:
|
1 2 3 4 5 |
SELECT MIN(StudentA) FROM Scores WHERE StudentA > 20 UNION SELECT MIN(StudentB) FROM Scores WHERE StudentB > 20 |
In the output you will see two rows, the first row will contain the minimum value from the “StudentA” table which is greater than 20. The second row contains the minimum value with the same condition from the “StudentB” table.
SQL MIN Across Multiple Columns
You can also find the minimum values across columns for all the rows in your table. For instance, the following script finds the minimum value between the tables “StudentA” and “StudentB” for each row in the Scores table that you created in a previous script. A new column “MinValue” is added which contains the minimum values across columns for all the rows.
|
1 2 3 4 5 6 7 |
SELECT StudentA, StudentB, (SELECT MIN(MinScore) FROM (VALUES (StudentA),(StudentB)) AS UpdateScore(MinScore)) AS MinValue FROM Scores |
Output:
GROUP BY Minimum Values
In this section, you will see how you can get the minimum value from each group of data in your database. For this section, you will be using the Northwind sample database which you can download and install from this link:
https://github.com/Microsoft/sql-server-samples/tree/master/samples/databases/northwind-pubs
Execute the following query to see the Products table:
|
1 2 3 4 |
USE Northwind SELECT * FROM Products |
Output:
From the above table, let’s try to find the minimum unit price for each product category.
Execute the following query:
|
1 2 3 4 5 6 |
USE Northwind SELECT CategoryID, MIN(UnitPrice) FROM Products GROUP BY CategoryID |
Output:
From the above output, you can see the minimum unit prices for all the products in various categories. For instance, you can see that for the category with id 2, the cheapest product costs 10.00.
HAVING Clause with SQL MIN for Filtering Records
You can also filter records using the SQL MIN function with the HAVING clause. The HAVING clause is normally used in conjunction with the GROUP BY clause and is used for filtering records based on an aggregate function.
For instance, if you want to select only those categories where the minimum unit price is greater than 7, you can use the following script:
|
1 2 3 4 5 6 7 |
USE Northwind SELECT CategoryID, MIN(UnitPrice) as MinPrice FROM Products GROUP BY CategoryID HAVING MIN(UnitPrice) > 7 |
Output:
From the about output, you can see that the selected records are grouped by the category ID only those categories are selected, where the minimum unit price is greater than 7.
ORDER BY Clause with SQL MIN for Ordering Records
The ORDER BY clause is used to sort selected records in ascending or descending order. The ORDER BY clause, when used in conjunction with the GROUP BY clause, can be used to order records via an aggregate function.
For instance, the following script selects the minimum value for unit price for each category, ordered in ascending order:
|
1 2 3 4 5 6 7 8 |
USE Northwind SELECT CategoryID, MIN(UnitPrice) as MinPrice FROM Products GROUP BY CategoryID HAVING MIN(UnitPrice) > 7 ORDER BY MIN(UnitPrice) |
Output:
Similarly, to sort the records in descending order, you just have to add a keyword DESC after the ORDER BY clause.
|
1 2 3 4 5 6 7 8 |
USE Northwind SELECT CategoryID, MIN(UnitPrice) as MinPrice FROM Products GROUP BY CategoryID HAVING MIN(UnitPrice) > 7 ORDER BY MIN(UnitPrice) DESC |
Output:
The above output shows the minimum unit price for each category in the Products table, ordered in the descending order of the minimum price.
Filtering Table Rows with Minimum Values in a Column
Often times you need to SELECT rows, based on the minimum value in a particular column. For instance, in the Products table of the Northwind database, you might want to select the name and the unit price of the product with minimum unit price.
To do so, you can write a subquery that uses the MIN function to return the minimum value for the unit price column. Next, in the outer query, you can use a SELECT query which returns the product name and unit price for the rows where the unit price is equal to the value (minimum value for the unit price) returned by the subquery.
|
1 2 3 4 5 6 |
USE Northwind SELECT ProductName, UnitPrice FROM Products WHERE UnitPrice = (SELECT MIN(UnitPrice) FROM Products) |
In the output below, you can see the product name with the minimum unit price.
Output:
SQL MIN on Categorical Columns
In addition to finding the minimum value from a numeric column, the SQL MIN function can also be applied to a categorical column. In this case, the SQL MIN function returns the first record from the set of records sorted in alphabetical order. Let’s see this in action.
The following script creates a Table named “Student” with columns “Student_Name” and “Student_Age”. The “Student_Name” column is a categorical column.
The script then selects all the records from the Student table alphabetically ordered by “Student_Name”.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE TABLE Student(Student_Name VARCHAR(50), Student_Age FLOAT) INSERT INTO Student VALUES ('John', 26), ('Nick', 15), ('Mick', 19), ('Alex', 35), ('Joseph',47) SELECT * FROM Student ORDER BY Student_Name |
Output:
Now if you apply the MIN function on the “Student_Name” column, the first record from the “Student_Name” column, sorted in alphabetical order, which is “Alex” in this case, will be displayed.
Here is the script to do so:
|
1 2 3 4 |
SELECT MIN(Student_Name) as MinName FROM Student |
Output:
Let’s see another example of how the SQL MIN function filters records in the case of categorical columns. Let’s get the minimum names from the Products table of the Northwind database, grouped by category ids.
|
1 2 3 4 5 6 |
USE Northwind SELECT CategoryID, MIN(ProductName) as MinName FROM Products GROUP BY CategoryID |
Output:
You can also use the SQL MIN with the ORDER BY clause to sort data using an aggregate function on a categorical column. Here is an example:
|
1 2 3 4 5 6 7 |
USE Northwind SELECT CategoryID, MIN(ProductName) as MinName FROM Products GROUP BY CategoryID ORDER BY MIN(ProductName) |
Output:
You can even use the SQL MIN function with the HAVING clause to filter data from categorical columns. For instance, the following script returns minimum product names from the ProductName column, sorted in alphabetical order, grouped by category id, where the minimum product name is smaller than the string “C”. This returns only those product names that start with either “A” or B. Here is an example:
|
1 2 3 4 5 6 7 |
USE Northwind SELECT CategoryID, MIN(ProductName) as MinName FROM Products GROUP BY CategoryID HAVING MIN(ProductName) < 'C' |
Output:
Conclusion
In this article, we learned how to work with the SQL MIN function which is used to find the minimum values in columns or rows in a table.
View all posts by Ben Richardson
- Working with the SQL MIN function in SQL Server - May 12, 2022
- SQL percentage calculation examples in SQL Server - January 19, 2022
- Working with Power BI report themes - February 25, 2021