TOP n Operator
Purpose
Specifies that either an explicit number of rows or an explicit percentage of rows is to be returned from the query result set.
Syntax
where:
|
Syntax element … |
Specifies … |
|
integer |
a nonzero, positive integer literal indicating the number of rows to return. As the size of n increases, the performance of TOP n queries gradually degrades. If the PERCENT option is specified, the system returns integer percent of the rows in the query result set, where 100 ≥ integer > 0. |
|
decimal |
a nonzero, positive decimal literal indicating the number of rows to return. The value of decimal must not exceed the maximum decimal value. As the size of n increases, the performance of TOP n queries gradually degrades. If the PERCENT option is specified, the system returns decimal percent of the rows in the query result set, where 100 ≥ decimal > 0. If the PERCENT option is not specified, decimal cannot include a decimal point. |
|
PERCENT |
that 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 |
that in addition to returning the specified number or percentage of rows in the ordered set produced by the ORDER BY clause, the query should return any rows where the sort value is the same as the sort value in the last row that satisfies the specified number or percentage of rows. The WITH TIES option only applies to a SELECT request that also specifies an ORDER BY clause. |
ANSI Compliance
TOP n is a Teradata extension to the ANSI SQL:2011 standard.
Other SQL dialects use similar operators with names such as:
What the TOP n Operator Does
You can use the TOP n operator in these ways:
SELECT TOP 10 *
FROM orders;
SELECT TOP 5 *
FROM orders
ORDER BY ship_date DESC;
Rules and Restrictions for the TOP n Operator
You cannot specify the TOP n operator in any of these SQL statements or statement components:
You cannot specify these options in a SELECT statement that specifies the TOP n operator:
This restriction refers to the WITH clause you can specify for summary lines and breaks. See “WITH Clause” on page 237. 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 60.
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 550.
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
For More Information
For more information about items related to the TOP n operator, see: