Window ordering is the arrangement of results (rows) within each window partition using a window order clause, which contains one or more value expressions separated by commas. If a window order clause is not specified, the input rows could be processed in an arbitrary order.
<WINDOW ORDER CLAUSE> ::= <ORDER SPECIFICATION>
The OLAP window order clause is different from the ORDER BY clause that can be appended to a nonwindowed query expression. See “Grammar rule 31”.
The ORDER BY clause in an OLAP function, for example, typically defines the expressions for sorting rows within window partitions; however, you can use the ORDER BY clause without a PARTITION BY clause, in which case the sort specification ensures that the OLAP function is applied to a meaningful (and intended) ordering of the intermediate result set.
An order specification is a prerequisite for the ranking family of OLAP functions; it is the ORDER BY clause, not an argument to the function itself, that identifies the measures for the ranking values. In the case of OLAP aggregates, the ORDER BY clause is not required in general, but it is a prerequisite to defining a window frame. (See “Window framing”.) This is because the partitioned rows must be sorted before the appropriate aggregate values can be computed for each frame.
The ORDER BY clause includes semantics for defining ascending and descending sorts, as well as rules for the treatment of NULL values. By default, OLAP functions assume an ascending order, where the lowest measured value is ranked 1.
Although this behavior is consistent with the default behavior of the ORDER BY clause that ends a SELECT statement, it is counterintuitive for most sequential calculations. OLAP calculations often require a descending order, where the highest measured value is ranked 1; this requirement must be explicitly stated in the ORDER BY clause with the DESC keyword.
Ranking
functions require a <window order clause> because they are defined
only over sorted input. As with an <order by clause> in a <query specification>,
the default sort sequence is ascending.
The use of a <window frame unit> of RANGE also requires the existence of a <window order clause>. In the case of RANGE, the <window order clause> may only consist of a single expression. See “Window framing.”
