TOP n Option Examples | SQL SELECT Statements | Teradata Vantage - Examples: Comparing the Results of Specifying TOP n * and TOP n WITH TIES * - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

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;

Result:

     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;

Result:

     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