WINDOW clause

In a SELECT statement, use the WINDOW clause to define all or part of a window for use with window functions such as AVG and RANK.

Syntax

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

Parameters

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 either in terms of a range of data values offset from the value in the current row (RANGE), or in terms of 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 in terms of 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 in terms of 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 in terms of 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.

Remarks

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 and the WINDOW clause.

Standards and compatibility

SQL/2003 SQL/2003 features T611, T612.

Example

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 SalaryWindow
FROM Employees
WINDOW SalaryWindow AS ( PARTITION BY State )
ORDER BY State, Surname;