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

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

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