O'Reilly logo

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

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required