Subject RE: [firebird-support] Re: RunSum in Firebird
Author Michael D. Spence
> -----Original Message-----
> From:
> [] On Behalf Of Halim B
> Sent: Wednesday, July 30, 2008 11:54 AM
> To:
> Subject: [firebird-support] Re: RunSum in Firebird
> 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.
> -Halim

I think you can do that sort of thing with a self-join, if you don't
have too much data or don't mind if it takes a while. I tried
this on a real table with ~60,000 rows:

First I created a view because it makes the self-join
easier to write ("possible" might be a better word than

create view TEMPVIEW(servid, amt) as
SELECT a."Serv ID", sum(a."Amount")
group by a."Serv ID"

Then I did this:

select v1.servid, v1.amt, sum(1) from tempview v1
join tempview v2 on (v1.SERVID <> v2.servid and v1.AMT < v2.amt)
or (v1.amt = v2.amt)
where v1.SERVID is not null
group by v1.servid, v1.amt order by 3

It took 26 seconds, so if you have a great many rows, it might
not serve. (My databases tend towards the miniscule, so I can
often get away with things<g>.)

Michael D. Spence
Mockingbird Data Systems, Inc.