FIRST_VALUE function

The FIRST_VALUE window aggregation function returns the value of a specified expression for the first row in each OLAP window partition.

Syntax

(1)
FIRST_VALUE function

1  FIRST_VALUE
2.1  ( expression )
2.2.1 
2.2.2.1 RESPECT NULLS
2.2.2.1 IGNORE NULLS
2.1  ( expression
2.2.1 RESPECT NULLS
2.2.1 IGNORE NULLS
2.1 ) %OVER clause for aggregation functions1
Notes:
Element Description Restrictions Syntax
expression Column name, alias, or constant expression If expression references a column, the column must also be in the select list of the Projection clause Column Expressions

Usage

The return data type of the FIRST_VALUE function is the data type of the specified expression. The result can be NULL. If IGNORE NULLS is specified, all rows where the expression value for the row evaluates to a NULL value are not considered in the calculation. If IGNORE NULLS is specified and all values in the OLAP window are NULL, the FIRST_VALUE function returns the NULL value.

The RESPECT NULLS or IGNORE NULLS option can be specified either within the parentheses immediately following the expression, or outside the parentheses, but only one such specification is allowed.

Example: FIRST_VALUE function

The following statement returns stock prices by day and the differences in the stock prices from the first value, 18.25, in the window partition.

SELECT price, price – FIRST_VALUE(price)
          OVER (PARTITION BY year ORDER BY tradingday)    
          AS diff_price
FROM stock_price
WHERE tradingday between ‘2012-11-01’ and ‘2012’-11-07’;

         price        diff_price
         18.25                 0
         18.37              0.12
                            
                            
         19.03              0.78
         18.59              0.34
         18.21             -0.04