OLAP window functions in the ORDER BY clause of SELECT statements

If an OLAP function clause is present in the ORDER BY clause, then the OLAP function is evaluated first, before the ORDER BY evaluation.

More generally, for simple SELECT statements that includes one or more OLAP window functions, the database server follows this sequence of processing:

• Apply any joins, filters, GROUP BY, or HAVING specifications to obtain the set of qualifying rows to return as the query result.
• Create window partitions of qualifying rows and apply the specified OLAP functions to each row in each partition (or to the entire query result set, if no partitions are defined).
• Apply the ORDER BY clause of the SELECT statement to the final query result.

For nested queries, each subquery follows the order above, but OLAP window partitions and their OLAP functions are applied to the result set of the innermost subquery in which the OLAP window is defined.

If the OLAP window includes a window ORDER clause, that clause, rather than the ORDER BY clause of the SELECT statement, defines the row numbers that the window ROW_NUMBER function assigns to the rows in partitions of the same OLAP window. The window ORDER clause does not, however, define the ordering of the query result set, which the ORDER BY clause of SELECT statement defines.

If the OLAP window does not include a window ORDER clause, the row numbers that the window ROW_NUMBER function assigns to the rows are in arbitrary order, as returned by the query or subquery, rather than according to any ORDER BY clause of the SELECT statement.