ORDER BY clause

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

ORDER BY expression [ASC|DESC] [NULLS (FIRST|LAST)], ...
Syntax rules
  • Sort columns can 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 can appear in the SELECT clause as the expression for an aliased column, or can reference columns from tables in the FROM clause. If the column reference is not in the SELECT clause, the query cannot 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 is removed by the Teiid optimizer.

  • If NULLS FIRST/LAST is specified, then nulls are guaranteed to be sorted either first or last. If the null ordering is not specified, then results will typically be sorted with nulls as low values, which is the default internal sorting behavior for Teiid. However, not all sources return results with nulls sorted as low values by default, and Teiid might 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 Teiid. It is best to use alias names in the ORDER BY clause.

results matching ""

    No results matching ""