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