Menu
Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

FIRST_VALUE and LAST_VALUE Window Functions

Given an ordered set of rows, FIRST_VALUE returns the value of the specified expression with respect to the first row in the window frame. The LAST_VALUE function returns the value of the expression with respect to the last row in the frame.

Syntax

Copy
FIRST_VALUE | LAST_VALUE ( expression [ IGNORE NULLS | RESPECT NULLS ] ) OVER ( [ PARTITION BY expr_list ] [ ORDER BY order_list frame_clause ] )

Arguments

expression

The target column or expression that the function operates on.

IGNORE NULLS

When this option is used with FIRST_VALUE, the function returns the first value in the frame that is not NULL (or NULL if all values are NULL). When this option is used with LAST_VALUE, the function returns the last value in the frame that is not NULL (or NULL if all values are NULL).

RESPECT NULLS

Indicates that Amazon Redshift should include null values in the determination of which row to use. RESPECT NULLS is supported by default if you do not specify IGNORE NULLS.

OVER

Introduces the window clauses for the function.

PARTITION BY expr_list

Defines the window for the function in terms of one or more expressions.

ORDER BY order_list

Sorts the rows within each partition. If no PARTITION BY clause is specified, ORDER BY sorts the entire table. If you specify an ORDER BY clause, you must also specify a frame_clause.

The results of the FIRST_VALUE and LAST_VALUE functions depend on the ordering of the data. The results are nondeterministic in the following cases:

  • When no ORDER BY clause is specified and a partition contains two different values for an expression

  • When the expression evaluates to different values that correspond to the same value in the ORDER BY list.

frame_clause

If an ORDER BY clause is used for an aggregate function, an explicit frame clause is required. The frame clause refines the set of rows in a function's window, including or excluding sets of rows in the ordered result. The frame clause consists of the ROWS keyword and associated specifiers. See Window Function Syntax Summary.

Data Types

These functions support expressions that use any of the Amazon Redshift data types. The return type is the same as the type of the expression.

Examples

See FIRST_VALUE and LAST_VALUE Window Function Examples.