LOCAL ORDER BY Clause - Teradata Database - Teradata Vantage NewSQL Engine

Orders qualified rows on each AMP in preparation to be input to a table function.

You can only specify a LOCAL ORDER BY clause for a query whose result is to be input to a table function.

You cannot specify a LOCAL ORDER BY clause with a derived table, view, or WITH clause object that specifies set operations.

LOCAL ORDER BY is a Teradata extension to the ANSI SQL:2011 standard.

LOCAL ORDER BY column_name

column_position

column_expression

Qualified rows are ordered on each AMP in preparation to be input to a table function.

You cannot specify a PARTITION BY ANY clause with a LOCAL ORDER BY clause in the same ON clause.

You cannot specify a DIMENSION clause with a LOCAL ORDER BY clause in the same ON clause.

If you use multiple ON clauses and you specify only LOCAL ORDER BY in one of them, all other ON clauses can only specify the DIMENSION option.

If you use multiple ON clauses, you cannot use a LOCAL ORDER BY clause in addition to a DIMENSION clause in the same ON clause.

If you specify multiple HASH BY clauses with LOCAL ORDER BY clauses, the following restrictions apply:

• All the clauses must have the same number of LOCAL ORDER BY columns.
• The data types of the columns must be the same type or matched using an implicit cast.

ASC

Results are to be ordered in ascending sort order.

If the sort field is a character string, the system orders it in ascending order according to the definition of the collation sequence for the current session.

The default order is ASC.

DESC

Results are to be ordered in descending sort order.

If the sort field is a character string, the system orders it in descending order according to the definition of the collation sequence for the current session.

NULLS FIRST

NULL results are to be listed first.

NULLS LAST

NULL results are to be listed last.