LOCAL ORDER BY Clause

Purpose

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

Syntax

where:

Syntax element …	Specifies …
column_name	the column name set by which rows are to be ordered locally on the AMPs before being input to a table function.
ASC	that the 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	that the 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	that NULL results are to be listed first.
NULLS LAST	that NULL results are to be listed last.

ANSI Compliance

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

Rules and Restrictions

The rules and restrictions are:

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

LOCAL ORDER BY must be specified as part of a FROM clause (see “FROM Clause” on page 96).

The scope of input to the LOCAL ORDER BY clause is limited to:

Derived tables

Views

WITH clause objects

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

You can specify a LOCAL ORDER BY clause either by itself or with a HASH BY clause.

If you specify both, the HASH BY clause must precede the LOCAL ORDER BY clause.

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 use multiple ON clauses and you specify only LOCAL ORDER BY in one of them, then all other ON clauses can only contain a DIMENSION clause.

If you specify a LOCAL ORDER BY clause with a HASH BY clause, then the following restrictions apply:

All of the ON 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 implicit cast.

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

Example : Local Ordering of Input Parameters to a Table Function

The following example illustrates the use of the LOCAL ORDER BY clauses for a table UDF. The table function add2int takes two integer values as input and returns both of them and their sum.

The query in this example selects all columns from add2int, which specifies that its input, dt, be value‑ordered on each AMP by dt.x1. Note that the specified local ordering might not be relevant to the add2int function and is only used for illustration.

The expected outcome of the query is that the rows on each AMP are sorted by the value of x1. The final hashed and sorted spool is used as the input to add2int.

     CREATE TABLE t1 (

       a1 INTEGER,

       b1 INTEGER);

     CREATE TABLE t2 (

       a2 INTEGER,

       b2 INTEGER);

     CREATE FUNCTION add2int (

       a INTEGER,

       b INTEGER)

     RETURNS TABLE (

       addend1 INTEGER,

       addend2 INTEGER,

       mysum INTEGER)

     SPECIFIC add2int

     LANGUAGE C

     NO SQL

     PARAMETER STYLE SQL

     NOT DETERMINISTIC

     CALLED ON NULL INPUT

     EXTERNAL NAME 'CS!add3int!add2int.c';

     WITH dt(x1,y1) AS (SELECT a1,b1

                        FROM t1)

     SELECT *

     FROM TABLE (add2int(dt.x1,dt.y1)

     LOCAL ORDER BY x1) AS tf;

For More Information

For more information about the use of the LOCAL ORDER BY clause, see:

“FROM Clause” on page 96

“HASH BY Clause” on page 109

“Example 2: HASH BY and LOCAL ORDER BY Clauses in the Same Statement” on page 110

“CREATE FUNCTION (Table Form)” in SQL Data Definition Language