ANSI Compliance - Teradata Database

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

Other SQL dialects use similar operators with names such as:

FIRST n

LIMIT n

SET ROWCOUNT n

STOP AFTER n

You can use the TOP n operator in these ways:

The TOP n operator returns only a subset of the rows in the query result set. For example, this query returns 10 rows from the orders table:

     SELECT TOP 10 * 

     FROM orders;

To obtain a subset of the data from an ordered set, specify the TOP n operator with an ORDER BY clause. For example, this query returns the last five orders shipped:

     SELECT TOP 5 * 

     FROM orders 

     ORDER BY ship_date DESC;

If the TOP n operator specifies a number greater than the number of rows in the query result set, then the query returns all of the rows in the query result set without returning an error.

As the size of n increases, the performance of TOP n gradually degrades.

You can use parameters to pass values for n from:

CREATE MACRO (see SQL Data Definition Language for more details).

CREATE PROCEDURE (SQL Form) (see SQL Data Definition Language for more details).

USING Request Modifier (see “USING Request Modifier” on page 510 for more details).

You cannot specify the TOP n operator in any of these SQL statements or statement components:

Correlated subquery

Subquery in a search condition

CREATE JOIN INDEX

CREATE HASH INDEX

Seed statement or recursive statement in a CREATE RECURSIVE VIEW statement or WITH RECURSIVE statement modifier

You cannot specify these options in a SELECT statement that specifies the TOP n operator:

DISTINCT option

QUALIFY clause

SAMPLE clause

WITH clause

This restriction refers to the WITH clause you can specify for summary lines and breaks. See “WITH Clause” on page 218. The nonrecursive WITH statement modifier that can precede the SELECT keyword can be included in statements that also specify the TOP n operator. See “WITH Statement Modifier” on page 43.

ORDER BY clause where the sort expression is an ordered analytical function.

Subselects of set operations.

You cannot specify the n value of a TOP n specification as a USING parameter for iterated array processing. For an example, see “Example 12: Non-Support for Iterated Requests With TOP n” on page 530.

The system evaluates the TOP n operator after all other clauses in the SELECT statement have been evaluated.

The QUALIFY clause with the RANK or ROW_NUMBER ordered analytical functions returns the same results as the TOP n operator.

For best performance, use the TOP option instead of the QUALIFY clause with RANK or ROW_NUMBER. In best case scenarios, the TOP n operator provides better performance; in worst case scenarios, the TOP n operator provides equivalent performance.

For example, these two statements have the same semantics, but the statement that specifies TOP n performs better than the statement that specifies QUALIFY ROW_NUMBER.

     SELECT TOP 10 * 

     FROM sales 

     ORDER BY county;

     SELECT * 

     FROM sales 

     QUALIFY ROW_NUMBER() OVER (ORDER BY COUNTY) <= 10;

Similarly, these two statements have the same semantics, but the statement that specifies TOP n performs better than the statement that specifies QUALIFY RANK.

     SELECT TOP 10 WITH TIES * 

     FROM sales ORDER BY county;

     SELECT * 

     FROM sales 

     QUALIFY RANK() OVER (ORDER BY county) <= 10;

Consider this data in the orders table:

     SELECT * 

     FROM orders;

     order_date  customer      product          quantity

     ----------  ------------  ------------  -----------

       04/05/10  Bestway       JR-0101                10

       04/04/28  Bestway       SW-0022                25

       04/05/10  Bestway       QR-7737                10

       04/04/28  Samstone      JR-0101                35

       04/05/10  Bestway       SW-0023                10

       04/04/28  Samstone      KB-6883                20

       04/05/10  Finelity      JR-0101                12

       04/04/28  Samstone      SW-0023                12

       04/05/10  Finelity      SW-0021                24

       04/05/10  Finelity      KB-8883                24

The following statement selects the top three orders with the largest quantities:

     SELECT TOP 3 * 

     FROM orders 

     ORDER BY quantity DESC;

     order_date  customer      product          quantity

     ----------  ------------  ------------  -----------

       04/04/28  Samstone      JR-0101                35

       04/04/28  Bestway       SW-0022                25

       04/05/10  Finelity      SW-0021                24

To include any orders with the same quantity as the third-largest, use the WITH TIES option:

     SELECT TOP 3 WITH TIES *

     FROM orders

     ORDER BY quantity DESC;

     order_date  customer      product          quantity

     ----------  ------------  ------------  -----------

       04/04/28  Samstone      JR-0101                35

       04/04/28  Bestway       SW-0022                25

       04/05/10  Finelity      SW-0021                24

       04/05/10  Finelity      KB-8883                24

For more information about options and functions related to the TOP n operator, see:

“QUALIFY Clause” on page 150

“RANK” and “ROW_NUMBER” in SQL Functions, Operators, Expressions, and Predicates