Excuse me,

I was quite short !

I retranscript from SQL Cookbook by Anthony Molinaro (Oreilly 2005)
To have a running sum in standard sql:

select e.emp_no
, e.first_name
, e.last_name
, e.salary
, (select sum(salary) from employee d where d.emp_no <= e.emp_no) as
running_sum
from employee e
where e.JOB_COUNTRY='USA'
order by 5

with window function

select e.emp_no
, e.first_name
, e.last_name
, e.salary
, (select sum(d.salary) from employee d where d.emp_no <= e.emp_no and
d.JOB_COUNTRY='USA') as running_sum
from employee e
where e.JOB_COUNTRY='USA'
order by 5

with window function

select e.emp_no
, e.first_name
, e.last_name
, e.salary
, sum(e.salary) over (order by e.salary, e.emp_no) as running_sum
from employee e
where e.JOB_COUNTRY='USA'
order by 4

the important keyword is OVER : it transforms aggregate functions like
sum, count, avg as window functions. they permit detail and aggregation
in the same query.
suppose you want to know the total number of employees for each dept
with each employee :

select last_name, dept_no, count(*) over (partition by deptno) as
empdept
from employee
order by 2

instead of

select e.last_name
, e.dept_no
, (select count(*) from employee d where d.dept_no = e.dept_no) as
empdept
from employee e
order by 2

Alexandre



--- In Firebird-Architect@yahoogroups.com, "Leyne, Sean" <Sean@...>
wrote:

>
> Alexandre,
>
> > Is there any plan to implement window functions (SQL-2003) ?
>
> Just so we all understand your request; please provide examples of the
> functions you are referring to.
>
>
> Sean
>