drop procedure row_number; 
create procedure row_number(initial integer) returning integer as row_number; 
define global i integer default  0 ; 
if initial =  0  then 

let i =  0 ; 

return  0  ; 
end if; 
let i = i+ 1 ; 

return i ; 
end procedure 
document "Simplified and similar function to row_number() from SqlServer.", 
"Need to reset the counter before each sql statement with ", 
"execute procedure row_number(0) OR declare into FROM clause", 
"For use in SELECT statement use any other number in parameter:", 
"select row_number(1) , tabname from systables;" 
"OR select row_number(1) , tabname from systables, table(row_number(0))" 
with listing in "/tmp/row_number.out" 
; 

Routine created. 

; 
-- The trick is call the same function as derived table.. in FROM clause 
select first  5  row_number( 1 ), tabname::char( 20 ) from systables, table(row_number( 0 )); 

(expression) (expression) 

 1  systables 

 2  syscolumns 

 3  sysindices 

 4  systabauth 

 5  syscolauth 

 5  row(s) retrieved. 

select first  5  row_number( 1 ), tabname::char( 20 ) from systables, table(row_number( 0 )); 

(expression) (expression) 

 1  systables 

 2  syscolumns 

 3  sysindices 

 4  systabauth 

 5  syscolauth 

 5  row(s) retrieved. 

Database closed.