An expression in the SELECT list that specifies how result sets are sorted. If you do not use this clause, result rows are returned unsorted.
where:
|
Syntax Element … |
Specifies … |
|
ORDER BY |
the order in which result rows are to be sorted. You can specify one or more sort columns on expression, column_name, column_name_alias, or column_position. The first sort column you specify determines the column used for the primary (first) sort. Any additional sort columns you specify determines the potential subsequent sorting based on the order in which you specify the columns. When multiple sort columns are specified, rows are sorted that have the same values of the previous sort column (or columns). You can specify a sort column to be sorted in either ascending or descending order. |
|
expression |
a valid SQL expression on which to sort rows. The expression can specify the numeric position of the expression in the expression list with a name or a constant. If the sort field is a character string, the expression used in the ORDER BY phrase can include a type modifier to force the sort to be either CASESPECIFIC or NOT CASESPECIFIC. You can specify scalar subqueries and scalar UDFs, but you cannot reference a column that has a BLOB, CLOB, ARRAY, or VARRAY data type in the ORDER BY expression list. |
|
column_name |
the name of a column on which to sort rows. An ordering column does not have to be specified as part of the select expression list. The maximum number of columns you can specify is 64. You can specify a column_name_alias instead of column_name (see the description of the column_name_alias element for details). The column_name_alias must not have the same name as a physical column in the table definition. If it does have the same name, you must specify column_position, not column_name_alias. See “Example 4: Ordering on a Column Name Alias” on page 214. The column you specify cannot have a BLOB, CLOB, ARRAY, or VARRAY data type. |
|
column_name_ |
a column name alias specified in the select expression list of the query for the column on which the result rows are to be sorted. If you specify a column_name_alias, that alias cannot match the name of any column that is defined in the table definition for any table referenced in the FROM clause of the query, whether that column is specified in the select list or not. The system references the underlying physical column having the name rather than the column that you attempt to reference using that same name as its alias. If you attempt to specify such an improper column_name_alias, the system aborts the request and returns an error. The workaround for this is to specify the sort column by its column_position value within the select list for the query. See “Rules and Restrictions for the ORDER BY Clause” on page 204. |
|
column_position |
the numeric position of a column or expression specified in the select expression list on which to sort. The value you specify must be a positive constant integer literal with a value between 1 and the number of columns specified in the select list, inclusive. Note that Teradata Database treats macro and procedure parameters as expressions, not as the specification of a column position. If the column_name_alias specified in the select list is the same as the name of the physical column in the underlying table specified in the FROM clause, then you cannot specify that column_name_alias as a sort value. You can specify a column position reference to order on the column or expression in the select list. However, you cannot use a column_name_alias that is the same as the name of some other column specified in the table definition. Substituting column_position for column_name_alias when the query will not otherwise work is not a good practice, and is not recommended. However, it does work if you must resort to it for a given situation. The column or expression referenced by its numeric position in the select-list must not have either a BLOB or CLOB data type. This is a Teradata extension to the ANSI SQL:2011 standard. |
|
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. |
The ORDER BY clause is ANSI SQL:2011-compliant with extensions. The specification of an ORDER BY clause in the definition of an updatable cursor is ANSI SQL:2011‑compliant.
The specification of an expression as an ordering argument in the ORDER BY clause is a Teradata extension to the ANSI SQL:2011 standard.
The rules and restrictions for the use of ORDER BY are:
You can also order on a column_name_alias.
The maximum number of column names you can specify is 64.
You cannot specify BLOB or CLOB columns in the column name list because you cannot sort on LOB values.
For details on defining case sensitivity for a column, see SQL Data Types and Literals.
You cannot specify BLOB or CLOB columns in the column position list because you cannot sort on LOB values.
When you specify both WITH and ORDER BY clauses in the same SELECT request, they function together as follows:
This is true regardless of the structure of the query or the number of WITH clauses. See “Example 3: Combining WITH and ORDER BY Clauses” on page 223.
This section describes some unexpected results that can occur when using the ORDER BY clause. They are: