Subject RE: [firebird-support] Serial number in a query result
Author Svein Erling Tysvær
>> Is there a sql function or any workaround to display a serial number
>> along with other columns (with sql query)?
>> for example,
>> SrNo Name Phone
>> 1 Abc 9848382
>> 2 cde 5837273
>> 3 efg 5478457
>> I searched through the net and found MS Sql server has something like this
>> select ROW_NUMBER()OVER(ORDERBY ColumnName1) AsSrNo
>> I just want to know whether anything like this is available in firebird.

>Window functions including the OVER clause etc. will be supported in
>Firebird 3. I think the only way to do this currently is to write a
>selectable stored procedure. Perhaps some tricky RDB$GET_CONTEXT /
>RDB$SET_CONTEXT mix can simulate this too.

I can think of at least three other ways that can work in some circumstances:

1) WITH RECURSIVE (somewhat complex, and will create an error due to too many recursions if more than x (around 1000?) rows are returned)
2) subselect counting the rows before the current row (possibly inefficient and doesn't work if you do not have an ORDER BY clause, but sometimes handy and simple to understand)
3) EXECUTE BLOCK (similar to stored procedures, but DML rather than DDL)