How to get record number or rank as part of dataset?

Sometimes you need to feed the SQL statement to some 3rd party tool or component and you need it to show the number of each row. While this can easily be done via temporary variable in a stored procedure or using EXECUTE BLOCK, sometimes you need it to be a single SELECT statement.

Firebird 3 will support SQL:2003 standard windowing functions for these purposes: ROW_NUMBER for simple sequential numbering, RANK for ranking with gaps and DENSE_RANK for ranking without gaps.

In Firebird 2, these functions can be simulated with ordinary aggregate functions and context variables or generators.

Assume a table PLAYERS which keeps track of the NAME and SCORE of each player. The following data illustrates the difference between ROW_NUMBER, RANK and DENSE_RANK:

Compared to ranking functions, ROW_NUMBER is relatively complicated
and error-prone. It may be simulated with a SEQUENCE or context
variable, in either case incrementing the "row_number" for each row
that is selected.

Example of using ROW_NUMBER with GENERATOR or a SEQUENCE:

SELECT NEXT VALUE FOR tmp$rn AS "row_number", players.*
FROM players
ORDER BY score DESC, name

Caveats: The sequence must be *pristine* before executing the above
statement, that is, either newly CREATEd or ALTERed to RESTART WITH 0.
If the sequence is not pristine, row numbering will not start at one.

Also, SEQUENCE names are global, meaning that concurrent queries using
the same SEQUENCE will appear to introduce gaps in row numbering.

Example of simulating ROW_NUMBER with a context variable:

SELECT rdb$get_context('USER_TRANSACTION', 'row#') AS "row_number",
dummy, -- don't exclude this, or dummy might be optimized away!
players.*
FROM players
CROSS JOIN
(SELECT rdb$set_context('USER_TRANSACTION', 'row#',
COALESCE(CAST(rdb$get_context('USER_TRANSACTION', 'row#') AS INTEGER), 0) + 1) AS dummy
FROM rdb$database) dummy
ORDER BY score DESC, name;

Caveats: The "row_number" context variable must be *pristine* before execution, which may be ensured by explicitly calling rdb$set_context(..., NULL) or by ending the current transaction with COMMIT or ROLLBACK. Again, row numbering will not start at one if the context variable is non-NULL and nonzero.

Also, rdb$get_context always returns a string, so you might need to CAST() "row_number" to an INTEGER if a string would confuse your application.

Note that it is possible to increment the context variable without a subquery, for example using CASE WHEN set_context(...) >= 0 THEN get_context(...).

Another approach relies on Firebird's implementation-defined (not standardized) right-to-left order evaluation of SELECTed columns. Here is an example of such code, contributed by Fabiano Bonin:

SELECT
rdb$get_context('USER_TRANSACTION', 'row#') as row_number,
rdb$set_context('USER_TRANSACTION', 'row#',
coalesce(cast(rdb$get_context('USER_TRANSACTION', 'row#') as integer), 0) + 1),
a.rdb$relation_name
FROM rdb$relations a
ORDER BY a.rdb$relation_name

The above code only works in Dialect 3 databases. For Dialect 1 databases you can use the following code, contributed by Serge Girard of developpez.net:

SELECT
rdb$get_context('USER_TRANSACTION', 'row#') as row_number,
rdb$set_context('USER_TRANSACTION', 'row#',
Coalesce(rdb$get_context('USER_TRANSACTION','row#'),0) + 1),
a.rdb$relation_name
FROM rdb$relations a
ORDER BY a.rdb$relation_name

Compared to ROW_NUMBER, RANK is easy. The RANK of a row is simply the
COUNT() + 1 of better-ranked rows. This can be computed with a
self-join:

SELECT p.name, p.score, COUNT(others.score) + 1 AS "rank"
FROM players p
LEFT JOIN players others ON others.score > p.score
GROUP BY 1, 2
ORDER BY "rank"

DENSE_RANK is similar RANK, except that the dense rank of a row considers
only the number of distinct better ranks:

SELECT p.name, p.score, COUNT(DISTINCT others.score) + 1 AS "dense_rank"
FROM players p
LEFT JOIN players others ON others.score > p.score
GROUP BY 1, 2
ORDER BY "dense_rank"


Most of this FAQ page was contributed by Mike Pomraning. Thanks, Mike.