MAX
Purpose
Returns a column value that is the maximum value for value_expression.
Syntax
where:
|
Syntax element … |
Specifies … |
|
ALL |
that all non-null values specified by value_expression, including duplicates, are included in the maximum value computation for the group. This is the default. |
|
DISTINCT |
that duplicate and non-null values specified by value_expression are eliminated from the maximum value computation for the group. |
|
value_expression |
a literal or column expression for which the maximum value is to be computed. The expression cannot contain any ordered analytical or aggregate functions. |
ANSI Compliance
This is ANSI SQL:2011 compliant.
MAXIMUM is a Teradata extension to the ANSI SQL:2011 standard.
Result Type and Attributes
The following table lists the default attributes for the result of MAX(x).
|
Attribute |
Value |
|||
|
Data Type |
If operand x is not a UDT, the result data type is the data type of operand x. If operand x is a UDT, the result data type is the data type to which the UDT is implicitly cast. |
|||
|
Format |
If operand x is not a UDT, the result data type is the data type of operand x. If operand x is a UDT, the result data type is the data type to which the UDT is implicitly cast. |
|||
|
Title |
Maximum(x) |
|||
Support for UDTs
By default, Teradata Database performs implicit type conversion on a UDT argument that has an implicit cast that casts between the UDT and any of the following predefined types:
To define an implicit cast for a UDT, use the CREATE CAST statement and specify the AS ASSIGNMENT clause. For more information on CREATE CAST, see SQL Data Definition Language.
Implicit type conversion of UDTs for system operators and functions, including MAX, is a Teradata extension to the ANSI SQL standard. To disable this extension, set the DisableUDTImplCastForSysFuncOp field of the DBS Control Record to TRUE. For details, see Utilities: Volume 1 (A-K).
For more information on implicit type conversion of UDTs, see Chapter 13: “Data Type Conversions.”
Usage Notes
MAX is valid for character data as well as numeric data. When used with a character expression, MAX returns the highest sort order.
Nulls are not included in the result computation. For more information, see SQL Fundamentals and “Aggregates and Nulls” on page 31.
If value_expression is a column expression, the column must refer to at least one column in the table from which data is selected.
The value_expression must not specify a column reference to a view column that is derived from a function.
MAX Window Function
For the MAX window function that computes a group, cumulative, or moving maximum value, see “Chapter 22 Ordered Analytical / Window Aggregate Functions” on page 969.
Example : CHARACTER Data
The following SELECT returns the immediately following result.
SELECT MAX(Name)
FROM Employee;
Maximum(Name)
-------------
Zorn J
Example : Column Expressions
You want to know which item in your warehouse stock has the maximum cost of sales.
SELECT MAX(CostOfSales) AS m, ProdID
FROM Inventory
GROUP BY ProdID
ORDER BY m DESC;
Maximum(CostOfSales) ProdID
-------------------- ------
1295 3815
975 4400
950 4120