TOP n - Teradata Database - Teradata Vantage NewSQL Engine

Specifies that either an explicit number of rows or an explicit percentage of rows is to be returned from the query result set.

Syntax Elements

TOP n

The query returns a specified number of rows or a percentage of available rows.

You cannot specify NORMALIZE with TOP n.

integer

integer PERCENT

A nonzero, positive INTEGER literal indicating the number of rows to return.

If the PERCENT option is specified, the system returns integer% of the total rows available, where 100 ≥ integer >  0.

decimal

decimal PERCENT

A nonzero, positive DECIMAL literal indicating the number of rows to return.

If the PERCENT option is specified, the system return decimal% of the total rows available, where 100 ≥ decimal  >  0.

If the PERCENT option is not specified, decimal cannot include a decimal point.

PERCENT

The integer or decimal value indicates a percentage of the rows to return.

The number of rows returned is (n/100 * result set row count), where n is the integer or decimal value.

If the number is not an integer, and the fractional part of the number ≥ 0.000000000000001, the next highest integer is used.

For fractions < 0.000000000000001, the number is truncated.

The TOP n operator is referred to as TOP m when you specify the PERCENT option for clarification purposes.

WITH TIES

Rows returned by the query include the specified number or percentage of rows in the ordered set produced by the ORDER BY clause, plus any additional rows where the value of the sort key is the same as the value of the sort key in the last row that satisfies the specified number or percentage of rows.

The WITH TIES option is ignored if the SELECT statement does not also specify an ORDER BY clause.

ANSI Compliance

TOP n is a Teradata extension to the ANSI SQL:2011 standard.