ORDER BY Clause Syntax | SQL SELECT Statements | Teradata Vantage - ORDER BY Clause Syntax - Advanced SQL Engine - Teradata Database

SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
qtb1554762060450.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata Vantage™
ORDER BY value_spec [,...]

Syntax Elements

value_spec
{ expression | column_name | column_name_alias | column_position }
  [ ASC |DESC ] [ NULLS { FIRST | LAST } ]
expression
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.
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).
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: Ordering on a Column Name Alias.
The column you specify cannot have a BLOB, CLOB, ARRAY, or VARRAY data type.
column_name_alias
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.
For an improper column_name_alias, the system 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 Column References and ORDER BY.
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 the 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.