|Subject||RE: [firebird-support] Re: RunSum in Firebird|
|Author||Michael D. Spence|
> -----Original Message-----I think you can do that sort of thing with a self-join, if you don't
> From: email@example.com
> [mailto:firstname.lastname@example.org] On Behalf Of Halim B
> Sent: Wednesday, July 30, 2008 11:54 AM
> To: email@example.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
> 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.
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")
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.