Joe Celko's Thinking in Sets: Auxiliary, Temporal, and Virtual Tables in SQL

12.6. The WINDOW Clause

Partition functions in SQL were developed by Oracle and IBM representatives on the SQL Standards Committee, and some of their work was also picked up in SQL Server 2005. The basic idea is to make aggregate functions work in a partition built by ordering the rows of a table. The syntax is a bit complicated with a lot of options to it.

Figure 12.4. Window Clause

Any of the usual aggregate functions (MIN (), MAX (), AVG(), SUM(), or COUNT()) simply has an OVER clause added to it, thus:

<Aggregate function> ([DISTINCT] <exp>) OVER
(<window clause>) [[AS] <column name>]

The WINDOW clause describes how a partition of rows ...