WINDOW clause

Use the WINDOW clause in a SELECT statement 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. This means that the appearance of result sets may vary depending on when you last accessed the row, and other factors.
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 rows offset from the current row (ROWS).
When using the RANGE clause, you must also use an ORDER BY clause. This is because the calculation performed to produce the window requires that the values be sorted. Additionally, the ORDER BY clause cannot contain more than one expression, and the expression must result in either a date or a numeric value.

When using the ROWS or RANGE clauses, if you specify only a starting row, the current row is used as the last row in the window. If you specify only an ending row, the current row is used as the first row.
- 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.
If you do not specify a ROW or a RANGE clause, the window size is determined as follows:
- If an ORDER BY clause is specified, the window starts with the first row in the partition (UNBOUNDED PRECEDING) and ends with the current row (CURRENT ROW).
- If an ORDER BY clause is not specified, the window starts with the first row in the partition (UNBOUNDED PRECEDING) and ends with last row in the partition (UNBOUNDED FOLLOWING).

Remarks

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

Depending on what you are trying to achieve with your results, you might specify all of the settings for a window in the WINDOW clause, and then name (refer to) the window from within the window function syntax (for example, AVG() OVER window-name). You could also specify the entire window in the window function and not use a WINDOW clause at all. Finally, you could also split the definition between the window function syntax, and the WINDOW clause. For example:

AVG() OVER ( windowA
             ORDER BY expression )...
...
WINDOW windowA AS ( PARTITION BY expression )

When splitting the window definition in this manner, the following restrictions apply:

You cannot use a PARTITION BY clause in the window function syntax.
You can use an ORDER BY clause in either the window function syntax or in the WINDOW clause, but not in both.
You cannot include a RANGE or ROWS clause in the WINDOW clause.

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, depending on what you are trying to achieve.

For more information about how to define and use windows in order to achieve the results you want, see Defining a window.

Standards and compatibility

SQL/2003 SQL/2003 features T611, T612.

Example

The following example returns an employee's salary as well as the average salary for all employees in that 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;