Teradata TOP Function

The TOP function is used to produce ‘N’ rows from the table based on specific criteria. Also it produces

• The TOP N percentage of row from the table.
• All the identical values if WITH TIES option is used with sorting order values.
• Rows in ordered or unordered manner.

Teradata TOP Function Syntax

TOP {[ INTEGER | DECIMAL ]} [PERCENTAGE] [WITH TIES]

Note – TOP N where N is an integer up to 18 digits in length.

Consider the following customer table.

Example 1

The below query returns only the two records from the customer table.

SELECT TOP 2 * FROM teradatapoint.cutomer;

*** Query completed. 2 rows found. 7 columns returned.
 *** Total elapsed time was 1 second.

    cust_id      income    age years_with_bank nbr_children gender marital_status
----------- ----------- ------ --------------- ------------ ------ --------
    1363329           0      6               0            0 F      1
    1362629           0      6               0            0 M      1

Example 2

The below query returns 2 rows but sorted on age.

SELECT TOP 2 cust_id,ageFROM teradatapoint.cutomer_savingsORDER BY 2 DESC;

*** Query completed. 2 rows found. 2 columns returned.
 *** Total elapsed time was 1 second.

    cust_id     age
-----------  ------
    1362672      50
    1362611      48

TOP N WITH TIES

SELECT TOP 3 WITH TIES cust_id,years_with_bank FROM teradatapoint.cutomer_savings ORDER BY 2 DESC;

*** Query completed. 5 rows found. 2 columns returned.
 *** Total elapsed time was 1 second.

    cust_id  years_with_bank
-----------  ---------------
    1362611                8
    1363355                1
    1362629                0
    1363329                0
    1362672                0

We can see from the above example that although we have specified only TOP 3, but it returned 5 rows. Because of the WITH TIES option it picked all the duplicate records for the third position.

The PERCENTAGE Option

The TOP N function can also produce a percentage of rows in addition to an absolute numbers of rows.

SELECT TOP 30 PERCENTcust_id,income FROM teradatapoint.cutomer_savings ORDER BY 2 DESC;

*** Query completed. 2 rows found. 2 columns returned.
 *** Total elapsed time was 1 second.

    cust_id       income
-----------  -----------
    1362611        24115
    1362672        16319