firebird 2.5 marking records with ordinal number

Question

I'd like to mark every occurrence of specific record with ordinal number. If in column 'id' I have six times the same value I need mark them as follows: first occurrence gets '1', second gets '2', third gets '3' in other column etc.

Thank in advance for any clue.

Answer 1

With Firebird 3 you can use the row_number window function, for example:

select row_number() over (order by id, some_column), id, other_columns
from mytable

Or if you want to restart counting for each id value:

select row_number() over (partition by id order by some_column), id, other_columns
from mytable

If you are stuck on Firebird 2.5 you will have to apply some tricks to do it:

One options is to use execute block (or a stored procedure) to add an extra incrementing value to a result set:

execute block
   returns (counter int, id int, other_columns varchar(32))
as
begin
    counter = 0;
    for select id, other_columns from mytable order by id, some_column into :id, :other_columns
    do
    begin
      counter = counter + 1;
      suspend;
    end
end

If you want to have a similar effect as partition by, then you need to keep track of the id value, and reset the counter when the value of id changes.

Another option would be to use temporary tables with a sequence + trigger to add a unique column. I have also seen solutions using a recursive CTE, but I don't exactly know how to do that myself (and IIRC you might run into some limits there).

Alternatively, if you are just looking for a way to uniquely identify the record, you can add the RDB$DB_KEY pseudo-column:

select rdb$db_key, id, other_columns 
from mytable 
order by id, some_column

The rdb$db_key uniquely identifies the record in the table (at minimum for the duration of the transaction).