15.00 - FIRST_VALUE / LAST_VALUE - 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)
Last Update
2018-09-24

FIRST_VALUE / LAST_VALUE

Purpose  

Returns the first value or last value in an ordered set of values.

Type

ANSI SQL:2011 window function.

Syntax  

where:

 

Syntax element …

Specifies …

value_expression

a column expression.

FIRST_VALUE and LAST_VALUE use the default data type of value_expression.

Larger numeric values are supported by casting them to a higher data type.

The expression cannot contain any ordered analytical or aggregate functions.

IGNORE NULLS

optional keyword that specifies not to return NULL values.

  • IGNORE NULLS (with FIRST_VALUE) = returns the first non-null value in the set, or NULL if all values are NULL.
  • If IGNORE NULLS (with LAST_VALUE) = returns the last non-null value in the set, or NULL if all values are NULL.
  • If all values are null, NULL is returned.

    RESPECT NULLS

    optional keyword that specifies whether to return NULL values

  • RESPECT NULLS (with FIRST_VALUE) = returns the first value, whether or not it is null.
  • RESPECT NULLS (with LAST_VALUE) = returns the last value, whether or not it is null.
  • If all values are null, NULL is returned.

    window

    a group, cumulative, or moving computation.

    For Window Aggregate Function syntax, see “Window Aggregate Functions” on page 984.

    In presence of ties in the sort key of the Window Aggregate Function syntax, FIRST_VALUE and LAST_VALUE are non-deterministic. They return value_expression from any one of the rows with tied order by values.

    Note: If the ROWS phrase is omitted and there is an ORDER BY phrase, the default ROWS is UNBOUNDED PRECEDING AND CURRENT ROW.

    If the ROWS phrase is omitted and there is no ORDER BY phrase, the default ROWS is UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

    If you want a default of ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING for LAST_VALUE (all rows in the partition), you need to set DIAGNOSTIC NONANSIFORLASTVALUE ON FOR SESSION.

    ANSI Compliance

    FIRST_VALUE and LAST_VALUE are Teradata extensions to the ANSI SQL:2011 standard.

    Usage Notes

    FIRST_VALUE and LAST_VALUE are especially valuable because they are often used as the baselines in calculations. For instance, with a partition holding sales data ordered by day, you may want to know how much the sales for each day were compared to the first sales day (FIRST_VALUE) for the period, or you may want to know, for a set of rows in increasing sales order, what the percentage size of each sale in the region was compared to the largest sale (LAST_VALUE) in the region.

    IGNORE NULLS is particularly useful in populating an inventory table properly.

    Selecting neither IGNORE NULLS or RESPECT NULLS is equivalent to selecting RESPECT NULLS.

    Example  

    The following example returns by start date the salary, moving average (ma), and first and last salary in the moving average group.

    Note: The functions are going to return the first/last value in the window. In the example, the first and last rows fall within the window. If the window were between 3 preceding and 2 preceding rows, you would see NULL for first value in the 1st two rows.

    SELECT start_date, salary,
           AVG(salary) OVER(ORDER BY start_date
           ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) ma,
           FIRST_VALUE(salary) OVER(ORDER BY start_date
           ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) first,
           LAST_VALUE(salary) OVER(ORDER BY start_date
           ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) last
    FROM employee
    ORDER BY start_date;
     

    start_date

    salary

    ma

    first

    last

    21-MAR-76

    6661.78

    6603.280

    6661.78

    6544.78

    12-DEC-78

    6544.78

    5183.780

    6661.78

    2344.78

    24-OCT-82

    2344.78

    4471.530

    6661.78

    2344.78

    15-JAN-84

    2334.78

    4441.780

    6661.78

    4322.78

    30-JUL-87

    4322.980

    4688.980

    6544.78

    7897.78

    31-DEC-90

    7897.78

    3626.936

    2344.78

    1234.56

    25-JUL-96

    1234.56

    3404.536

    2334.78

    1232.78

    17-SEP-96

    1232.78

    3671.975

    4322.78

    1232.78