FirebirdSQL logo
 Aggregate FunctionsSystem Packages 

ROW_NUMBER Examples

select
  id,
  salary,
  row_number() over (order by salary)
from employee
order by salary;
Result
id salary rank
-- ------ ----
 3  8.00     1
 4  9.00     2
 1 10.00     3
 5 10.00     4
 2 12.00     5
See also

[fblangref50-windowfuncs-dense-rank], [fblangref50-windowfuncs-rank]

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>