RunSum in Firebird

Halim B. wrote:

This is an example of what I want. I have a table called "Sales" with two fields ItemDescription and totalsales. Sale

ItemDescription       TotalSales
Coke                  $500.00
Water                 $800.00
Pepsi                 $300.00
Orange Juice          $600.00

I would like to get the following results

ItemDescription       TotalSales   Rank
Coke                  $500.00      3
Water                 $800.00      1
Pepsi                 $300.00      4
Orange Juice          $600.00      2

Rank column is sorted based on the highest TotalSales. Using ElevateDB database engine I used to run this query

Select *, RunSum(1) from Sales Order by TotalSales Desc.

I am trying to do the same thing with firebird.

Adriano dos Santos Fernandes answers:

You may use ROWNUM-like generic SP procedure, like this one. I do it based on the link sent here.

create sequence rownum_seq!

create or alter procedure rownum
   returns (rowid bigint)
as
begin
   rowid = gen_id(rownum_seq, 1);
   rdb$set_context('USER_TRANSACTION', 'rownum' || rowid, 0);
   suspend;
end!

create or alter procedure rownum_inc (rowid bigint)
   returns (rownum bigint)
as
begin
   rownum = cast(rdb$get_context('USER_TRANSACTION', 'rownum' || rowid) as bigint) + 1;
   rdb$set_context('USER_TRANSACTION', 'rownum' || rowid, rownum);
   suspend;
end!

And use in your queries:

select *
  from (
    select
       (select rownum from rownum_inc(rowid)) rank,
       Sales.*
    from Sales, rownum
    order by TotalSales desc
  )
order by ItemDescription

You don't need the outer select...order by if you can order by TotalSales DESC.