> -----Original Message-----
> From: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com] On Behalf Of Halim B
> Sent: Wednesday, July 30, 2008 11:54 AM
> To: firebird-support@yahoogroups.com
> 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
"easier"):

create view TEMPVIEW(servid, amt) as
SELECT a."Serv ID", sum(a."Amount")
FROM ARTRANS a
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.