15.00 - MIN - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)

MIN

Purpose  

Returns a column value that is the minimum 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 minimum value computation for the group. This is the default.

DISTINCT

that duplicate and non-null values specified by value_expression are eliminated from the minimum value computation for the group.

value_expression

a literal or column expression for which the minimum value is to be computed.

The expression cannot contain any ordered analytical or aggregate functions.

ANSI Compliance

This is ANSI SQL:2011 compliant.

MINIMUM 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 MIN(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.

Title

Minimum(x)

Format

If operand x is not a UDT, the result format is the format of operand x.

If operand x is a UDT, the result format is the format of the data type to which the UDT is implicitly cast.

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:

  • Numeric
  • Character
  • Byte
  • DATE
  • TIME or TIMESTAMP
  • Interval
  • 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 MIN, 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

    MINIMUM is valid for character data as well as numeric data. MINIMUM returns the lowest sort order of a character expression.

    The computation does not include nulls. For more information, see “Manipulating Nulls” in SQL Fundamentals and “Aggregates and Nulls” on page 31.

    If value_expression specifies a column expression, the expression must refer to at least one column in the table from which data is selected.

    If value_expression specifies a column reference, the column must not be a view column that is derived from a function.

    MIN Window Function

    For the MIN window function that computes a group, cumulative, or moving minimum value, see “Window Aggregate Functions” on page 984.

    Example : MINIMUM Used With CHARACTER Data

    The following SELECT returns the immediately following result.

       SELECT MINIMUM(Name) 
       FROM Employee; 
       
       Minimum(Name)
       -------------
       Aarons A    

    Example : JIT Inventory

    Your manufacturing shop has recently changed vendors and you know that you have no quantity of parts from that vendor that exceeds 20 items for the ProdID. You need to know how many of your other inventory items are low enough that you need to schedule a new shipment, where “low enough” is defined as fewer than 30 items in the QUANTITY column for the part.

       SELECT ProdID, MINIMUM(QUANTITY)
       FROM Inventory
       WHERE QUANTITY BETWEEN 20 AND 30
       GROUP BY ProdID
       ORDER BY ProdID;

    The report is as follows:

            ProdID  Minimum(Quantity)
       -----------  -----------------
              1124                 24
              1355                 21
              3215                 25
              4391                 22