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.

cust_id income age years_with_bank nbr_children gender marital_status
1363355 13087 22 1 0 M 2
1363329 0 6 0 0 F 1
1362672 16319 50 0 1 F 2
1362629 0 6 0 0 M 1
1362611 24115 48 8 1 F 2

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