If you can read Spanish, this article shows several methods for having row numbers in the SELECT.
https://firebird21.wordpress.com/2013/05/13/numerando-las-filas-de-un-select/ Greetings. Walter. On Thu, Mar 19, 2015 at 5:28 AM, masb...@za-management.com [firebird-support] <firebird-support@yahoogroups.com> wrote: > > > Hi all, > I have the need to implement something like a row number and read the last > thread about the autoincrement number which also led me to this FAQ: > http://www.firebirdfaq.org/faq343/ > > > I now tried something like this: > > select > cast(rdb$get_context('USER_TRANSACTION', 'row#2') as integer) as > row_number, > rdb$set_context('USER_TRANSACTION', 'row#2', > coalesce(cast(rdb$get_context('USER_TRANSACTION', 'row#2') as integer), 0) > + 1) as SET_CONTEXT, > <other columns> > from mytable > > Which works fine. The first thing I now did is to create a derived table > to get rid of the SET_CONTEXT part. I now experienced something very > strange to me. > > When I did this: > > select s.* from > (select > cast(rdb$get_context('USER_TRANSACTION', 'row#2') as integer) as > row_number, > rdb$set_context('USER_TRANSACTION', 'row#2', > coalesce(cast(rdb$get_context('USER_TRANSACTION', 'row#2') as integer), 0) > + 1) as SET_CONTEXT, > <other columns> > from mytable) s > > all is fine and row numbers are fine. > > But when I explicitely call the individual rows like this; > > select > s.row_number, > s.<other columns> > from > (select > cast(rdb$get_context('USER_TRANSACTION', 'row#2') as integer) as > row_number, > rdb$set_context('USER_TRANSACTION', 'row#2', > coalesce(cast(rdb$get_context('USER_TRANSACTION', 'row#2') as integer), 0) > + 1) as SET_CONTEXT, > <other columns> > from mytable) > > then the row numbers have all the same value namely the last counter > position (highest last value). > > Am I doing something wrong? > > Kind regards Christian > > > > >
