17.10 - ORDER BY Clause Usage - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
Publication ID
English (United States)

You can specify an ORDER BY clause in a view definition only if you also specify either the TOP n or TOP m PERCENT option. Without a TOP n clause, the results on which the ORDER BY should be applied is ambiguous, and therefore not meaningful semantically.

An ORDER BY clause is semantically meaningful when used with a TOP n subselect operation because it affects the actual results of the subselect in the view definition. For TOP n subselect operations, the ORDER BY specification also controls the final result of the subselect operation.

If you want to create a view that also includes the WITH TIES option for TOP n, then you must also specify an ORDER BY clause; otherwise, the system ignores the WITH TIES specification.