Min function returns the minimum value in the expression. It can be followed by the OVER clause. Min can be used with numeric, character, and datetime columns, but not with bit columns. Min ignores any null values. For character columns, Min finds the lowest value in the sort sequence.
Syntax of MIN Function :
MIN ( [ ALL | DISTINCT ] expression )
ALL applies the aggregate function to all values. ALL is the default.
DISTINCT means each unique value is considered.
Expression is a constant, column name, or function, and any combination of arithmetic, bitwise, and string operators. Aggregate functions and subqueries are not permitted.
It Returns a value same as expression.
Example of MIN Funtion :
Example 1 : Using min function in select query.
SELECT MIN(Subtotal) AS MinimumOrder
FROM Order_Subtotals
Output
MinimumOrder
12.50
Above example returns lowest order amount from Order_Subtotals table.
Example 2 : Using min function with subquery in a where clause
SELECT OrderID, Subtotal
FROM Order_Subtotals
WHERE Subtotal > (SELECT MIN(SaleAmount)
FROM Sales_Totals_by_Amount)
ORDER BY Subtotal DESC;
Output
OrderID Subtotal
10515 9921.30
10691 10164.80
10540 10191.70
10479 10495.60
10897 10835.24
10817 10952.84
10417 11188.40
Above query displays orders with a subtotal greater than min saleamount in Sales_Totals_by_Amount table. The subquery returns a
single value i.e the minimal SaleAmount, which could be compared with a single value from the outer query.
You can achieve same result using ANY / SOME logical operator in the subquery. To view this example,please visit below link.
ANY / SOME Operator