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

>
> Alexandre,
>
> > 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
>
> I don't see the window function, in this example.
>
> I also don't see these statements as equivalent.

I have paste two times ! and changed a little,
the second query is not a window example !
When I ran the query, I saw a side effect due to currency values
and added a restriction on job_country in the aggregate query.

>
>
> > 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.
>
> While I don't know the syntax, I don't see why "e.salary" is part

of the

> order by clause.
>
>
> Sean
>

I have just read the examples from Anthony Molinaro book and frankly,
I never used such syntax.
I think order by e.salary in over clause is here to be consistent
with order by 4.

As far as I know, only Oracle 10g, DB2 9 (and MSSQL partially) have
support for window functions.

You could read examples from

http://www.oreilly.com/catalog/sqlpg2/chapter/index.html


Alexandre