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 Usage » OLAP Support » Window functions » Defining a window

Defining a window Next Page

Sizing the window

The current row provides the reference point for determining the start and end points of a window. The window bounds that you specify define a window relative to the current row.

You can specify the bounds as either an exact number of rows, using the ROWS clause, or as a range of values offset from the value in the current row, using the RANGE clause. In the latter case, the size of the window can vary, depending on the values in the surrounding rows.

Setting bounds by specifying the number of rows (ROWS clause)

Following are some additional examples of window bounds you might specify.

Setting bounds by specifying a range (RANGE clause)

When using the RANGE clause, you define the window size based on values in the column specified in the ORDER BY clause. To use the RANGE clause, you must also specify an ORDER BY clause, the ORDER BY clause must contain only a single column, and the column must be in the number domain. The window size is then determined by comparing values in the column specified in the ORDER BY clause to the value in the current row.

For example, suppose that for the current row, the column specified in the ORDER BY clause contains the value 10. If you specify the window size to be RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING, you are specifying the size of the window to be as large as required to ensure that the first row contains a 5 in the column, and the last row in the window contains a 15 in the column. As the window moves down the partition, the size of the window may grow or shrink according to the size required to fulfill the range specification.

RANGE uses unsigned integer values. Truncation of the range expression may occur depending on the domain of the ORDER BY expression and the domain of the value specified in the RANGE clause.