Home
SQL programmingYou can use the HCL OneDB™ implementation of the SQL language to develop applications for HCL OneDB™ database servers.
Guide to SQL: SyntaxThe HCL OneDB™ Guide to SQL: Syntax describes the syntax of the statements, data types, expressions, operators, and built-in functions of the HCL OneDB™ dialect of the SQL language.
SQL statementsThis chapter describes the syntax and semantics of SQL statements that are recognized by HCL OneDB™.
SELECT statementUse the SELECT statement to retrieve values from a database or from an SPL or collection variable. A SELECT operation is called a query.
ORDER BY ClauseThe ORDER BY clause sorts query results by specified columns or expressions.
OLAP window functions in the ORDER BY clause of SELECT statementsYou can include OLAP window functions in the final ORDER BY clause of SELECT statements that do not include the CONNECT BY clause.

OLAP window functions in the ORDER BY clause of SELECT statements

You can include OLAP window functions in the final ORDER BY clause of SELECT statements that do not include the CONNECT BY clause.

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.