SQL Server MIN Function

The MIN() function in SQL Server is a type of aggregate function. It is used to get the minimum or smallest value of a specified column or expression. It is the most commonly used built-in function in the SQL Server. It comes under the numeric function that accepts only one parameter and returns a single result after evaluation to summarize the input data set. We can use this function with several clauses such as WHERE, GROUP BY, ORDER BY, and HAVING clause to get the filtered output.

Syntax

The following syntax explain the MIN() function in SQL Server:

The function parameters are explained below:

aggregate_expression: It is the required expression that represents the column name from which the lowest value is returned.

table_name(s): It indicates the name of a table from where we want to retrieve columns. There can be specified more than one table.

WHERE conditions: It is an optional clause to define a condition to filter the column or expressions.

DISTINCT: It returns the lowest of the unique values in the expression. However, it does not affect the MIN() function and produces the same result without using this keyword.

NOTE: The MIN() function ignores NULL values.

MIN() Function Example

Let us understand how the MIN function works in SQL Server with the help of various examples. Consider our database has a table named "ProductQty" that contains the following data:

Example1: This example uses the MIN() function to get the minimum quantity of the sold products from the table:

Executing the statement will display the result of minimum values in all rows:

Example2: This example uses the WHERE clause with the MIN() function. This clause is used to filter the records from the table. The below statement will display the product name with the lowest quantity sold:

Here, SQL Server first evaluates the subquery that uses the MIN() function and then the outer query display the product name whose quantity is equal to the result of the subquery. Executing the statement will return the desired output:

Example3: This example uses the GROUP BY clause with the MIN() function. This clause collects data from multiple rows and groups them based on single or multiple columns. The below statement will return the minimum quantity in all rows from the ProductQty table for each product group.

Executing the statement will display the minimum quantity of each product:

Example4: This example uses the HAVING clause with the MIN() function. This clause must be used with the GROUP BY clause to filter the grouped records from the table. The below statement will display the product name with a minimum quantity greater than 200.

Executing the statement will return the below output:

Example5: This example uses the DISTINCT clause with the MIN() function. This clause is used to remove the duplicate rows from the column. The below statement returns the minimum sold product quantity by removing the duplicates data from the "Quantity" column of a "ProductQty" table.

Here is the result:

Example6: This example uses the MIN() function with two columns to get the lowest resultant output:

Executing the statement will return the lowest values of (Quantity * Price) from the ProductQty table:

Example7: This example uses the OVER() clause with the MIN() function. This clause determines which rows from the query are applied to the function, in what order the function evaluates them, and when the function's calculations should restart.

Executing the query will return the below result:

Conclusion

This article will explain a complete overview of the MIN() function in SQL Server. Here we have learned how to get the lowest values from the table with the help of WHERE, GROUP BY, HAVING, and OVER clauses.