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.
- integer
- integer PERCENT
- A nonzero, positive INTEGER literal indicating the number of rows to return.
- decimal
- decimal PERCENT
- A nonzero, positive DECIMAL literal indicating the number of rows to return.
- PERCENT
- The integer or decimal value indicates a percentage of the rows to return.
- 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.
ANSI Compliance
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
What the TOP n Operator Does
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 .
- CREATE PROCEDURE (SQL Form), see SQL Data Definition Language .
- USING Request Modifier, see USING Request Modifier.
Rules and Restrictions for the TOP n Operator
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”. 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.
- 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: Non-Support for Iterated Requests With TOP n.
Evaluation Order of TOP n in a SELECT
The system evaluates the TOP n operator after all other clauses in the SELECT statement have been evaluated.
TOP n Operator Out Performs QUALIFY RANK and QUALIFY ROW_NUMBER
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;
Example: Comparing the Results of Specifying TOP n * and TOP n WITH TIES *
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
Related Topics
- “QUALIFY Clause”
- “RANK” and “ROW_NUMBER” in SQL Functions, Operators, Expressions, and Predicates