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 context variables can do the job for you here. Here's an interesting way to do it contributed by Fabiano Bonin. This example is showing all the tables and views in the database.

Example for Dialect 3 databases:

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

Example for Dialect 1 databases:

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

Dialect 1 example was contributed by Serge Girard of developpez.net.

You might want to display rank instead of just record number. For example, you might have a table of players each having some points and you want players with same amount of points to have the same rank. Something like this:

Rn Player Points
1. Jordan 45
2. Pippen 22
2. Bryant 22
etc.

You can use code like this:

SELECT
rdb$get_context('USER_TRANSACTION', 'rec_no') AS No,
rdb$set_context('USER_TRANSACTION', 'rec_no',
COALESCE(CAST(rdb$get_context('USER_TRANSACTION', 'rec_no') AS INTEGER), 0)
+ 1) AS X1,

rdb$get_context('USER_TRANSACTION', 'player_rank') AS Rank,
rdb$set_context('USER_TRANSACTION', 'player_rank', P.POINTS) AS X2,
rdb$set_context('USER_TRANSACTION', 'player_rank',
COALESCE(CAST(rdb$get_context('USER_TRANSACTION', 'player_rank') AS
INTEGER), 0) +
CASE WHEN COALESCE(rdb$get_context('USER_TRANSACTION', 'rank_value'),
0)<>P.POINTS THEN 1 ELSE 0 END) AS X3,

P.NAME, P.POINTS

FROM
PLAYERS P
ORDER BY P.POINTS DESC

This example of ranking is provided by Karol Bieniaszewski.