Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Reference » SQL Statements

WHILE statement [T-SQL] Next Page

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.


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 :
| unsigned-integer PRECEDING

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

window-frame-bound :
| 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.

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.

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.

If you do not specify a ROW or a RANGE clause, the window size is determined as follows:


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:

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.

See also
Standards and compatibility

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;