Firebird 2.5 tagging with serial number

Question

I want to mark each occurrence of a particular record with a serial number. If I have the same value six times in the id column, I need to mark them as follows: the first occurrence gets "1", the second gets "2", the third gets "3" in another column, etc.

Thanks in advance for any hint.

Answer 1

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

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

Or if you want to restart the count for each id value:

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

If you are stuck in Firebird 2.5, you will need to apply some tricks:

execute block ( ) :

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

, , id reset id.

+ . CTE, , ( IIRC ).

, , - RDB$DB_KEY:

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

RDB$DB_KEY ( , ).