Halim B escreveu:

> 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.
>

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.


Adriano