WINDOW clause

WINDOW window-expression, ...

window-expression : new-window-name AS ( window-spec )

window-spec :
[ existing-window-name ]
[ PARTITION BY expression, ... ]
[ ORDER BY expression [ ASC | DESC ], ... ]
[ { ROWS | RANGE } { window-frame-start | window-frame-between } ]

window-frame-start :
{ UNBOUNDED PRECEDING
 | unsigned-integer PRECEDING
 | CURRENT ROW }

window-frame-between :
BETWEEN window-frame-bound1 AND window-frame-bound2

window-frame-bound :
window-frame-start
| UNBOUNDED FOLLOWING 
| unsigned-integer FOLLOWING

PARTITION BY clause The PARTITION BY clause organizes the result set into logical groups based on the unique values of the specified expression. When this clause is used with window functions, the functions are applied to each partition independently. For example, if you follow PARTITION BY with a column name, the result set is partitioned by distinct values in the column.
If this clause is omitted, the entire result set is considered a partition.

The PARTITION BY expression cannot be an integer literal.
ORDER BY clause The ORDER BY clause defines how to sort the rows in each partition of the result set. You can further control the order by specifying ASC for ascending order (the default) or DESC for descending order.
The ORDER BY expression cannot be an integer literal.

If this clause is omitted, SQL Anywhere returns rows in whatever order is most efficient, and the appearance of result sets may vary depending on when you last accessed the row.
ROWS clause and RANGE clause Use either a ROWS or RANGE clause to express the size of the window. The window size can be one, many, or all rows of a partition. You can express the size of the window as a range of data values offset from the value in the current row (RANGE), or the number of physical rows offset from the current row (ROWS).
When using the RANGE clause, you must also specify an ORDER BY clause because range calculations require values to be sorted. The ORDER BY clause for ranges must contain one expression, and that expression must result in either a date or a numeric value.

If you do not specify a ROWS or RANGE clause, the database server uses default window sizes based on whether an ORDER BY clause is present. For information about the defaults, see Defining a window.
- PRECEDING clause Use the PRECEDING clause to define the first row of the window using the current row as a reference point. The starting row is expressed as the number of rows preceding the current row. For example, 5 PRECEDING sets the window to start with the fifth row preceding the current row.
  Use UNBOUNDED PRECEDING to set the first row in the window to be the first row in the partition.
- BETWEEN clause Use the BETWEEN clause to define the first and last row of the window, using the current row as a reference point. First and last rows are expressed as the number of rows preceding and following the current row, respectively. For example, BETWEEN 3 PRECEDING AND 5 FOLLOWING sets the window to start with the third row preceding the current row, and end with the fifth row following the current row.
  Use BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to set the first and last rows in the window to be the first and last row in the partition, respectively. This is equivalent to the default behavior if no ROW or RANGE clause is specified.
- FOLLOWING clause Use the FOLLOWING clause to define the last row of the window using the current row as a reference point. The last row is expressed as the number of rows following the current row.
  Use UNBOUNDED FOLLOWING to set the last row in the window to be the last row in the partition.

The WINDOW clause must appear before the ORDER BY clause in a SELECT statement.

With the exception of the LIST function, all aggregate functions can be used as window functions. However, ranking aggregate functions (RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST, and ROW_NUMBER) require an ORDER BY clause, and do not allow a ROW or RANGE clause in the WINDOW clause or inline definition. For all other window functions, you can use any of the clauses.

For more information about defining and using windows to achieve the results you want, see Defining a window and Window definition: inlining using the OVER clause and WINDOW clause.

SQL/2008 The WINDOW clause and window aggregate functions comprise SQL/2008 optional language features T611, "Elementary OLAP operations", and T612, "Advanced OLAP operations". The window functions FIRST_VALUE and LAST_VALUE are vendor extensions.

The following example returns an employee's salary and the average salary for all employees in the selected state. The results are ordered by state and then by surname.

SELECT EmployeeID, Surname, Salary, State,
  AVG( Salary ) OVER Salary_Window
FROM Employees
WINDOW Salary_Window AS ( PARTITION BY State )
ORDER BY State, Surname;

WINDOW clause

Syntax

Parameters

Remarks

See also

Standards and compatibility

Example