How to create a functionality similar to row_number with partition?

Question

I followed some tutorials on the internet I created a functionality similar to row_number. However, that's not my requirement, I need to simulate the partition by option. Thew following is what I have now. Upgrading the Firebird version is not an option as I am dealing with legacy governmental affairs.

WITH c AS 
(
select
   rdb$set_context('USER_TRANSACTION', 'SEQ', 
   cast(COALESCE(rdb$get_context ('USER_TRANSACTION', 'SEQ'), 0) AS integer) + 1) s,
   CAST (rdb$get_context('USER_TRANSACTION', 'SEQ') AS integer) g FROM
   rdb$database
   )

SELECT 
 (SELECT c.g FROM c WHERE c.s >= 0) seq,
 id_exercicio
 FROM LICITACAO_PROCESSO GROUP BY    id_exercicio, ID_MODALIDADE ORDER BY ID_EXERCICIO, ID_MODALIDADE 

I want to partition by id_exercicio

I want to reinitiate the count at every "change" of id_exercicio.

Answer 1

You indeed better join with a stored procedure, with table values as its input parameter. Notice, you would need to do a LEFT JOIN or RIGHT JOIN to do it, https://firebirdfaq.org/faq143/ otherwise you would be getting stored_proc(NULL) rows too, and you do not want them.

Based upon your query the SP would look something like that

create procedure sub_counter(id integer not null)
  returns (sub_id integer not null)
as
  declare old_id integer;
begin
  old_id = coalesce( cast(
     rdb$get_context( 'USER_TRANSACTION', 'SEQ_id' ) as integer ), -1 );
  if ( :old_id <> :id ) then
  begin
    rdb$set_context( 'USER_TRANSACTION', 'SEQ_id', :id );
    rdb$set_context( 'USER_TRANSACTION', 'SEQ_cnt', 0 );
  end

  sub_id = 1 + CAST( rdb$get_context('USER_TRANSACTION', 'SEQ_cnt') AS integer );
  rdb$set_context ('USER_TRANSACTION', 'SEQ_cnt', :sub_id );

  suspend;
end

and then

SELECT 
 sp.sub_id,
 t.id_exercicio
FROM LICITACAO_PROCESSO t 
left join sub_counter(t.id_exercicio) sp 
  on 1=1
ORDER BY ID_EXERCICIO

or, safer

WITH c AS(
 SELECT 
   t.id_exercicio
 FROM LICITACAO_PROCESSO t 
 ORDER BY ID_EXERCICIO
)
SELECT 
 sp.sub_id,
 c.id_exercicio
FROM c 
left join sub_counter(c.id_exercicio) sp 
  on 1=1
order by 2,1