Ben Richardson
Products table from Northwind Database

Working with the SQL MIN function in SQL Server

May 12, 2022 by

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:

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.

The following SELECT query shows all the columns in the table:

student table for dummy records

Let’s use the MIN function to find the minimum value in the “StudentA” column:

Output:

result of basic MIN

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.

Output:

result of SQL MIN

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.

Output:

Result of SQL MIN 2

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:

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.

Output:

SQL MIN Across multiple columns

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:

Output:

Products table from Northwind Database

From the above table, let’s try to find the minimum unit price for each product category.

Execute the following query:

Output:

MIN with GROUP BY Clause

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:

Output:

Result of HAVING Clause with SQL MIN for Filtering Records

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:

Output:

Result of ORDER BY Clause with SQL MIN for Ordering Records

Similarly, to sort the records in descending order, you just have to add a keyword DESC after the ORDER BY clause.

Output:

Result of ORDER BY descending Clause with SQL MIN for Ordering Records

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.

In the output below, you can see the product name with the minimum unit price.

Output:

Result of Filtering Table Rows with Minimum Values

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


Output:

Result of SQL MIN on Categorical Columns

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:

Output:

Result of SQL MIN on Categorical Columns 2

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.

Output:

Result of SQL MIN on Categorical Columns with GROUP BY

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:

Output:

Result of SQL MIN on Categorical Columns with ORDER BY

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:

Output:

Result of SQL MIN on Categorical Columns with HAVING clause

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.

Ben Richardson
168 Views