16.20 - TOP - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Syntax and Examples

Teradata Database
Teradata Vantage NewSQL Engine
Release Number
March 2019
English (United States)
Last Update

Returns the top-ranked rows to from the SELECT operation.

You cannot specify a TOP n operator if the view definition also specifies any of the following:

  • DISTINCT operator
  • SAMPLE clause
  • QUALIFY clause
  • Subquery, that is, a subquery within a view definition cannot contain a TOP operator.

Views that specify a TOP operator are not updatable.

You can also specify an ORDER BY clause with the TOP operator. Otherwise, ORDER BY clauses are not valid within view definitions.

The sort expression for the ORDER BY clause cannot contain BLOBs, CLOBs, UDTs, columns with Period or Geospatial types, or ordered analytic functions.

TOP performs as well or better as the ordered analytic functions QUALIFY RANK() or QUALIFY ROW_NUMBER() that can be used in a similar fashion.

A DECIMAL or an INTEGER value for the number of rows to return from the SELECT operation.
Note that this can be more limiting than the TOP n clause you can specify with DML requests, depending on whether you specify an ORDER BY clause or not. If you do not specify an ORDER BY clause, then a TOP n clause only specifies that any n base table rows be returned, not the TOP n.
Returns only m percent of rows from the SELECT operation, where m can be either a DECIMAL or an INTEGER value.
If ( m *COUNT(*)/100.0) does not evaluate to an integer value, then the system rounds it up to the next highest integer value.
For example, if ( m *COUNT(*)/100.0) evaluates to 0.1, then it is rounded up to 1.
If ( m *COUNT(*)/100.0) evaluates to 9.2, then it is rounded up to 10.
Teradata Database only applies this rounding up to the first 15 digits following the decimal point. For example, if ( m *COUNT(*)/100.0) evaluates to 0.0000000000000001, then the system does not round it up to 1.