MIN (Transact-SQL)

Article
05/16/2013

Returns the minimum value in the expression. May be followed by the OVER clause.

Topic link icon Transact-SQL Syntax Conventions

Syntax

MIN ( [ ALL | DISTINCT ] expression )

Arguments

ALL
Applies the aggregate function to all values. ALL is the default.
DISTINCT
Specifies that each unique value is considered. DISTINCT is not meaningful with MIN and is available for ISO compatibility only.
expression
Is a constant, column name, or function, and any combination of arithmetic, bitwise, and string operators. MIN can be used with numeric, char, varchar, uniqueidentifier, or datetime columns, but not with bit columns. Aggregate functions and subqueries are not permitted.

For more information, see Expressions (Transact-SQL).

Return Types

Returns a value same as expression.

Remarks

MIN ignores any null values.

With character data columns, MIN finds the value that is lowest in the sort sequence.

Examples

A. Simple example

The following example returns the lowest (minimum) tax rate.

USE AdventureWorks2012;
GO
SELECT MIN(TaxRate)
FROM Sales.SalesTaxRate;
GO

Here is the result set.

-------------------

5.00

(1 row(s) affected)

B. Using the OVER clause

The following example uses the MIN, MAX, AVG and COUNT functions with the OVER clause to provide aggregated values for each department in the HumanResources.Department table.

USE AdventureWorks2012; 
GO
SELECT DISTINCT Name
       , MIN(Rate) OVER (PARTITION BY edh.DepartmentID) AS MinSalary
       , MAX(Rate) OVER (PARTITION BY edh.DepartmentID) AS MaxSalary
       , AVG(Rate) OVER (PARTITION BY edh.DepartmentID) AS AvgSalary
       ,COUNT(edh.BusinessEntityID) OVER (PARTITION BY edh.DepartmentID) AS EmployeesPerDept
FROM HumanResources.EmployeePayHistory AS eph
JOIN HumanResources.EmployeeDepartmentHistory AS edh
     ON eph.BusinessEntityID = edh.BusinessEntityID
JOIN HumanResources.Department AS d
 ON d.DepartmentID = edh.DepartmentID
WHERE edh.EndDate IS NULL
ORDER BY Name;