10.7. Navigational Functions

The navigational functions get the simple (non-aggregated) value of an expression from another row of the query, within the same partition.

☝
Important

FIRST_VALUE, LAST_VALUE and NTH_VALUE also operate on a window frame. For navigational functions, Firebird applies a default frame from the first to the current row of the partition, not to the last. In other words, it behaves as if the following frame is specified:

  |RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

This is likely to produce strange or unexpected results for NTH_VALUE and especially LAST_VALUE, so make sure to specify an explicit frame with these functions.

Example of Navigational Functions
   |select
   |    id,
   |    salary,
   |    first_value(salary) over (order by salary),
   |    last_value(salary) over (order by salary),
   |    nth_value(salary, 2) over (order by salary),
   |    lag(salary) over (order by salary),
   |    lead(salary) over (order by salary)
   |  from employee
   |  order by salary;

Results 

  |id  salary  first_value  last_value  nth_value     lag    lead
  |--  ------  -----------  ----------  ---------  ------  ------
  |3     8.00         8.00        8.00     <null>  <null>    9.00
  |4     9.00         8.00        9.00       9.00    8.00   10.00
  |1    10.00         8.00       10.00       9.00    9.00   10.00
  |5    10.00         8.00       10.00       9.00   10.00   12.00
  |2    12.00         8.00       12.00       9.00   10.00  <null>

10.7.1. FIRST_VALUE()

First value of the current partition

Result typeThe same as type as expr

Syntax 

  |FIRST_VALUE ( <expr> ) OVER <window_name_or_spec>

Table 10.3. Arguments of FIRST_VALUE
ArgumentDescription

expr

Expression. May contain a table column, constant, variable, expression, scalar function. Aggregate functions are not allowed as an expression.

See alsoSection 10.7.3, “LAST_VALUE()”, Section 10.7.5, “NTH_VALUE()”

10.7.2. LAG()

Value from row in the current partition with a given offset before the current row

Result typeThe same as type as expr

Syntax 

  |LAG ( <expr> [, <offset [, <default>]])
  |  OVER <window_name_or_spec>

Table 10.4. Arguments of LAG
ArgumentDescription

expr

Expression. May contain a table column, constant, variable, expression, scalar function. Aggregate functions are not allowed as an expression.

offset

The offset in rows before the current row to get the value identified by expr. If offset is not specified, the default is 1. offset can be a column, subquery or other expression that results in a positive integer value, or another type that can be implicitly converted to BIGINT. offset cannot be negative (use LEAD instead).

default

The default value to return if offset points outside the partition. Default is NULL.

The LAG function provides access to the row in the current partition with a given offset before the current row.

If offset points outside the current partition, default will be returned, or NULL if no default was specified.

10.7.2.1. LAG Examples

Suppose you have RATE table that stores the exchange rate for each day. To trace the change of the exchange rate over the past five days you can use the following query.

   |select
   |  bydate,
   |  cost,
   |  cost - lag(cost) over (order by bydate) as change,
   |  100 * (cost - lag(cost) over (order by bydate)) /
   |    lag(cost) over (order by bydate) as percent_change
   |from rate
   |where bydate between dateadd(-4 day to current_date)
   |and current_date
   |order by bydate

Result 

  |bydate     cost   change percent_change
  |---------- ------ ------ --------------
  |27.10.2014  31.00 <null>         <null>
  |28.10.2014  31.53   0.53         1.7096
  |29.10.2014  31.40  -0.13        -0.4123
  |30.10.2014  31.67   0.27         0.8598
  |31.10.2014  32.00   0.33         1.0419

See alsoSection 10.7.4, “LEAD()”

10.7.3. LAST_VALUE()

Last value from the current partition

Result typeThe same as type as expr

Syntax 

  |LAST_VALUE ( <expr> ) OVER <window_name_or_spec>

Table 10.5. Arguments of LAST_VALUE
ArgumentDescription

expr

Expression. May contain a table column, constant, variable, expression, scalar function. Aggregate functions are not allowed as an expression.

See also note on frame for navigational functions.

See alsoSection 10.7.1, “FIRST_VALUE()”, Section 10.7.5, “NTH_VALUE()”

10.7.4. LEAD()

Value from a row in the current partition with a given offset after the current row

Result typeThe same as type as expr

Syntax 

  |LEAD ( <expr> [, <offset [, <default>]])
  |  OVER <window_name_or_spec>

Table 10.6. Arguments of LEAD
ArgumentDescription

expr

Expression. May contain a table column, constant, variable, expression, scalar function. Aggregate functions are not allowed as an expression.

offset

The offset in rows after the current row to get the value identified by expr. If offset is not specified, the default is 1. offset can be a column, subquery or other expression that results in a positive integer value, or another type that can be implicitly converted to BIGINT. offset cannot be negative (use LAG instead).

default

The default value to return if offset points outside the partition. Default is NULL.

The LEAD function provides access to the row in the current partition with a given offset after the current row.

If offset points outside the current partition, default will be returned, or NULL if no default was specified.

See alsoSection 10.7.2, “LAG()”

10.7.5. NTH_VALUE()

The Nth value starting from the first or the last row of the current frame

Result typeThe same as type as expr

Syntax 

  |NTH_VALUE ( <expr>, <offset> )
  |  [FROM {FIRST | LAST}]
  |  OVER <window_name_or_spec>

Table 10.7. Arguments of NTH_VALUE
ArgumentDescription

expr

Expression. May contain a table column, constant, variable, expression, scalar function. Aggregate functions are not allowed as an expression.

offset

The offset in rows from the start (FROM FIRST), or the last (FROM LAST) to get the value identified by expr. offset can be a column, subquery or other expression that results in a positive integer value, or another type that can be implicitly converted to BIGINT. offset cannot be zero or negative.

The NTH_VALUE function returns the Nth value starting from the first (FROM FIRST) or the last (FROM LAST) row of the current frame, see also note on frame for navigational functions. Offset 1 with FROM FIRST is equivalent to FIRST_VALUE, and offset 1 with FROM LAST is equivalent to LAST_VALUE.

See alsoSection 10.7.1, “FIRST_VALUE()”, Section 10.7.3, “LAST_VALUE()”