15.10 - TOP - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

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.

n
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.
m PERCENT
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.