Teradata Max

In Teradata Max function is used to get maximum value for a value_expression.

SYNTAX:

MAXIMUM([TYPE] value_expression)  [Teradata extension to the ANSI SQL:2011 standard]
MAX([TYPE] value_expression)

Where value_expression is a literal or column expression for which the maximum value is to be computed. The expression cannot contain any ordered analytical or aggregate functions.

TYPE as 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.

TYPE as DISTINCT => that duplicate and non-null values specified by value_expression are eliminated from the maximum value computation for the group.

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.

Example of Teradata Max: CHARACTER Data

The following SELECT returns the immediately following result.

   SELECT MAX(Name) 
   FROM Employee; 
 

OUTPUT:

   Maximum(Name)
   -------------
   Zorn J

Example of Teradata Max: 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;

Output:

   Maximum(CostOfSales)  ProdID
   --------------------  ------
                   1295    3815
                    975    4400
                    950    4120