The ORDER BY clause specifies how records should be sorted. The options are ASC (ascending) and DESC (descending).


ORDER BY expression [ASC|DESC] [NULLS (FIRST|LAST)], ...

Syntax Rules

  • Sort columns may be specified positionally by a 1-based positional integer, by SELECT clause alias name, by SELECT clause expression, or by an unrelated expression;
  • Column references may appear in the SELECT clause as the expression for an aliased column or may reference columns from tables in the FROM clause. If the column reference is not in the SELECT clause the query must not be a set operation, specify SELECT DISTINCT, or contain a GROUP BY clause;
  • Unrelated expressions, expressions not appearing as an aliased expression in the select clause, are allowed in the order by clause of a non-set QUERY. The columns referenced in the expression must come from the from clause table references. The column references cannot be to alias names or positional;
  • The ORDER BY columns must be of a comparable type;
  • If an ORDER BY is used in an inline view or view definition without a limit clause, it will be removed by the Data Virtuality Server optimizer;
  • If NULLS FIRST/LAST is specified, nulls are guaranteed to be sorted either first or last. If the null ordering is not specified, the results will typically be sorted with nulls as low values, which is the Data Virtuality Server's internal default sorting behaviour. However, not all sources return results with nulls sorted as low values by default, and the Data Virtuality Server may return results with different null orderings.

    The use of positional ordering is no longer supported by the ANSI SQL standard and is a deprecated feature in the Data Virtuality Server. It is preferable to use alias names in the ORDER BY clause.