17.05 - order_by_specification - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1144-175K
Language
English (United States)

Order in which result rows are to be sorted.

You can only specify an ORDER BY clause for a view definition if you also specify either the TOP n or the TOP m PERCENT operators.

For more information about the ORDER BY clause, see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.

expression
An expression in the subselect expression list, either by name, or by means of a constant that specifies the numeric position of the expression in the expression list.
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 cannot specify BLOB, CLOB, UDT, Period, or Geospatial columns or ordered analytic functions in the ORDER BY expression list.
column_name
Names of columns used in the ORDER BY clause in the subselect expression. These can be ascending or descending.
You cannot specify BLOB, CLOB, UDT, Period, or Geospatial columns in the ORDER BY column list.
column_name_alias
A column name alias specified in the select expression list of the view definition for the column on which the result rows are to be sorted.
If you specify a column_name_alias to sort by, then that alias cannot match the name of any column that is defined in the view definition for any table or view referenced in the FROM clause of the view definition whether that column is specified in the select list or not. This does not work because the system always 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 specify an improper column_name_alias, the system returns an error to the requestor.
The workaround for this is to specify the sort column by its column_position value within the select list for the view definition. See ORDER BY in Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.
column_position
Numeric position of the columns specified by the ORDER BY clause. These can be ascending or descending.
ASC
Ascending sort order.
The default order is ASC.
If a sort option is not specified, result values are sorted in ascending order according to the client system collating sequence.
If ORDER BY is not specified, rows are returned unsorted.
DESC
Descending sort order.
If ORDER BY is not specified, rows are returned unsorted.