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>

`FIRST_VALUE()`

First value of the current partition

Result type

The same as type as expr

Syntax

FIRST_VALUE ( <expr> ) OVER <window_name_or_spec>

Table 1. Arguments of `FIRST_VALUE`
Argument	Description
expr	Expression.May contain a table column, constant, variable, expression, scalar function.Aggregate functions are not allowed as an expression.