NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
M-1
M
MAX Function
MAX is a function that determines the maximum value within a set of values. The type
of the result depends on the type of the argument.
[ ALL ] expression
specifies an expression that indicates the set of values from which to determine a
maximum value.
The expression must include a value from each row of the result table (that is, at
least one column from the result table), and cannot include the COUNT, AVG, MIN,
or SUM functions, or another MAX function. For example,
MAX (SALARY)
MAX (PARTCOST * QTY_ORDERED)
ALL is an optional keyword that does not change the meaning of the clause. SQL
uses all rows (whether or not you specify ALL) unless you use the DISTINCT
clause, described next.
DISTINCT column
specifies a set of distinct column values from each row of the result table to
determine a maximum value. The column cannot be a column from a view that
corresponds to an expression in the view definition.
If you specify DISTINCT in more than one MAX function in the same statement,
the functions must reference the same column.
Duplicate rows are eliminated only if you specify DISTINCT; otherwise, all rows
are included, whether or not you specify ALL.
Specifying DISTINCT with the MAX function does not restrict the use of
DISTINCT with AVG, SUM, MIN, or COUNT.
Considerations—MAX
•
Collations for character arguments
If you specify an expression or column with a character data type as the argument to
MAX, the collation used for the comparison is the collation associated with the
argument. SQL uses a binary comparison if no collation is associated with the
argument.
•
Null values
MAX { ( [ ALL ] expression ) }
{ ( DISTINCT column ) }