MAX

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

Arguments:

DISTINCT

Specifies that MAX returns the maximum value of unique values.
DISTINCT is not meaningful with MAX 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 maximum 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 MAX 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. MAX

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

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

B. MAX and GROUP BY

The following query returns 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,
        MAX(time-taken)
FROM ex031118.log
GROUP BY PageType

See also:

Aggregate Functions

Aggregating Data Within Groups

© 2004 Microsoft Corporation. All rights reserved.