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.
Ascending and Descending OrdersYou can use the ASC and DESC keywords to specify ascending (smallest value first) or descending (largest value first) order.

Ascending and Descending Orders

You can use the ASC and DESC keywords to specify ascending (smallest value first) or descending (largest value first) order.

The default order is ascending. For DATE and DATETIME data types, smallest means earliest in time and largest means latest in time. For character data types in the default locale, the order is the ASCII collating sequence, as listed in Collating Order for U.S. English Data.

For NCHAR or NVARCHAR data types, the localized collating order of the current session is used, if that is different from the code set order. For more information about collation, see SET COLLATION statement.

If you specify the ORDER BY clause, NULL values by default are ordered as less than values that are not NULL. Using the ASC order, a NULL value comes before any non-NULL value; using DESC order, the NULL comes last.

Specifying the order of NULL values

The ORDER BY clause can include the NULLS FIRST keywords or the NULLS LAST keywords to show explicitly (or else to override) the default sort order of NULL values:

The NULLS FIRST keywords instruct the database server to put NULL values first in the sorted query results. In an ascending sort, the ASC NULLS FIRST keywords request the default order. In a descending sort, DESC NULLS FIRST specifies that rows with a NULL value in the sort key column precede non-NULL rows in the sorted result set.
The NULLS LAST keywords instruct the database server to put NULL values last in the sorted query results. In a descending sort, the DESC NULLS LAST keywords request the default order. In an ascending sort, ASC NULLS LAST specifies that rows with a NULL value in the sort key column follow non-NULL rows in the sorted result set.