How to use MIN Function in SQL Server

In this SQL Server tutorial, you will learn about the MIN function in SQL Server, which is the aggregate function that can find the minimum value. Then, you will understand the syntax of the MIN() function and its definition.

Afterwards, you will learn to use the MIN() function with DATE and VARCHAR data types. Finally, you will understand how to use the MIN() function with the GROUP BY and HAVING clause.

Table of Contents

MIN Function in SQL Server

MIN function in SQL Server finds the minimum value from the set of values. In other words, if you have any column in your table and want to know the highest value of that column, then the MIN() function can return the lowest value from that column.

The MIN() function aids businesses and allows you to find the minimum salary, lowest sales, newest date, etc.

The syntax is given below.

MIN( [ ALL | DISTINCT ] expression )

Where,

MIN(): This function finds the lowest value from the columns.
expression: It is an expression and can be any numeric, datetime columns, character, and uniqueidentifier, but it can’t be the aggregate function, subqueries, and bit columns.
ALL | DISTINCT: You can use these options with the MIN() function to find the minimum value from the specified columns.
- If you use the MIN(ALL expression) like this, it applies the MIN() function to all the values of the selected columns and returns the minimum value.
- In the MIN(DISTINCT expression) case, the MIN() function is applied to the unique values of the columns and returns the minimum value.

The return type of the MIN() function is the same as the input expression.

The MIN() function does not consider null values when calculating the minimum value.
The MIN() function returns a NULL value when no row is selected.
MIN() returns the lowest value in the collating sequence for character columns.

Let’s take an example and find the minimum value from the columns. Suppose you have a ProductSales table containing the product’s sale price, shown below.

MIN Function in SQL Server Product_Sales Table

Find the minimum sale price of the product in the SalePrice column. So use the below query for that.

SELECT MIN(SalePrice) ProductMINSalePrice FROM PRODUCT_SALES;

When executing the above query, it returns the minimum sale price of the product as 59999.99 dollars. Here, you have applied the MIN() function on the SalePrice column that contains the numeric values.

This is how you can retrieve the minimum value from the table columns using the MIN function in SQL Server.

MIN Function in SQL Server with Date Data Type

MIN() also works on date columns. For example, consider a Sales table containing the product name, sale date, and sale price, shown below.

Look at the column SaleDate that shows the sale date of each product if you want to find the earlier sale date of the product. Then, use the below query.

SELECT MIN(SaleDate) AS EarlierSaleDate FROM Sales;

The MIN() function returns the earlier date from the column SaleDate. As a result, it returns with the date 2023-01-01. Thus, the MIN() function also worked on the date value.

This is how you can use the MIN function in SQL Server with date datatype.

MIN Function in SQL Server with Character Data Type

When the MIN() function is applied to the columns that contain the string value, it sorts the string values alphabetically and returns the last value from the sorted string values.

For example, let’s say you have a CustomerOrders table containing OrderID, CustomerName, and OrderDate, shown below.

MIN Function in SQL Server with Character Data Type CustomerOrders Table

Now, return the customer name that appears alphabetically in the CustomerName column of the CustomerOrders table. For that, use the below query.

SELECT MIN(CustomerName) FirstCustomerNameAlphabetically 
FROM CustomerOrders;

MIN Function in SQL Server with Character Data Type

The result of the above query shows that the Customer with the name Aaron is the first alphabetically. So here, when you use the MIN() function with string values ( or character values), it sorts those values alphabetically and returns the first string value.

This is how to use the MIN() function in SQL Server with string values.

MIN Function in SQL Server with GROUP BY Clause

The GROUP BY clause separates similar data into a group. When the MIN() function and the GROUP BY clause are used, it returns the minimum value for each group separately.

The general syntax for using the MIN() function with the GROUP BY clause is below.

SELECT column_name1, MIN(column_name2) 
FROM table_name
GROUP BY column_name1;

Where,

MIN(column_name2): It returns the minimum value of column_name2 for each group.
GROUP BY column_name1: Specify the column name you would like to use for grouping the data.

For example, you have table Sales with columns sale_id, product_id, sale_amount, and sale_date, shown below.

MIN Function in SQL Server with GROUP BY Clause Sales Table

The above table shows that each product has multiple sales records with different sales amounts. You must find each product’s minimum sale by-product or sale amount. For that, use the below query.

SELECT product_id, MIN(sale_amount) AS MinSaleByProduct
FROM Sales
GROUP BY product_id

MIN Function in SQL Server with GROUP BY Clause

After executing the above query, it groups the sales by product_id and returns the minimum sale amount for each product. For example, the minimum sale amount for the P100 is 150.00, P103 is 190.00, etc.

First, each sale amount is grouped according to the product ID using the GROUP BY product_id. Then, MIN(sale_amount) is applied to each group and returns the minimum sale amount from that group.

This is how to use the GROUP BY clause with the MIN function in SQL Server.

MIN Function in SQL Server with HAVING clause

As you know, the HAVING clause is a filter that filters the data groups based on the aggregate functions. Here, you will filter the group created by the GROUP BY clause with certain conditions.

For example, use the same table Sales you used in the above section. So, in the above section, you returned the minimal sale amount by product. Here, you need to find the minimum sale amount for each product, but only those with less than 200.00.

So use the below query.

SELECT product_id, MIN(sale_amount) AS MinSaleByProduct
FROM Sales
GROUP BY product_id
HAVING MIN(sale_amount) < 200;

MIN Function in SQL Server with HAVING clause

As you can see, there are only three products with a minimum sale amount of 150.00, 190.00, and 195.00, which is less than 200.00.

First, the sale amount is grouped according to their product ID using the GROUP BY product_id. Then, the minimum sales amount is retrieved from each group using MIN(sale_amount).

After that, each group based on the product_id is filtered for the sale amount of less than 200.00 using the HAVING MIN(sale_amount) < 200.

This is how you can use the MIN() function with the HAVING clause to filter the group-based data.

Conclusion

In this SQL Server tutorial, you learned how to return the minimum value from the columns using the MIN() function in SQL Server. Also, you have applied the MIN() function on a column containing the date and string values. Ultimately, you learned how to use the MIN() function with the HAVING and GROUP BY clauses.