MIN

MIN ( [ DISTINCT | ALL ] <field_expr> )

Returns the minimum value among all the values of the specified field-expression.

Arguments:

DISTINCT

Specifies that MIN returns the minimum value of unique values.
DISTINCT is not meaningful with MIN and is available for SQL-92 compatibility only.
DISTINCT can only be used when the query does not make use of the GROUP BY clause.

ALL

Applies the aggregate function to all values. ALL is the default.

<field_expr>

The field-expression among whose values the minimum is to be found.
The field-expression can be of any data type.

Return Type:

The returned type is the same as the argument field-expression.

Remarks:

• NULL values are ignored by the MIN aggregate function.
• Aggregate functions are allowed as field-expressions only in the SELECT, HAVING, and ORDER BY clauses.
• The arguments of an aggregate function can not reference other aggregate functions.
• The arguments of an aggregate function can not reference the following functions:
  • SEQUENCE
  • OUT_ROW_NUMBER
• DISTINCT is allowed in aggregate functions only when there is no GROUP BY clause.

Examples:

A. MIN

The following query returns the size of the smallest executable file in the "system32" directory, using the FS input format:

SELECT MIN(Size)
FROM C:\windows\system32\*.*
WHERE TO_LOWERCASE(EXTRACT_EXTENSION(Name)) = 'exe'

B. MIN and GROUP BY

The following query returns the shortest and the longest time spent by each page extension logged in the specified IIS W3C log file:

SELECT  TO_LOWERCASE(EXTRACT_EXTENSION(cs-uri-stem)) AS PageType,
        MIN(time-taken),
        MAX(time-taken)
FROM ex031118.log
GROUP BY PageType

See also:

COUNT
SUM
AVG
MAX
PROPCOUNT
PROPSUM
GROUPING

Aggregate Functions

Aggregating Data Within Groups

---

© 2004 Microsoft Corporation. All rights reserved.