Teradata TOP n Operator

Teradata TOP n Operator

TOP n operator can be used to return specific number/percentage of rows from the query resultset. TOP n operator can be used in 3 ways:

• TOP n
• TOP n PERCENT
• TOP n WITH TIES

General Syntax: TOP n [PERCENT | WITH TIES]


➠ Restrictions for the TOP n operator

• Cannot be used in subquery.
• Cannot be used along with DISTINCT option.
• Cannot be used along with SAMPLE clause.
• Cannot be used along with QUALIFY clause.
• Cannot be used in the definition create HASH INDEX and JOIN INDEX.

➠ TOP n :Value of n must be positive integer.

• Simple TOP n: This will return random 'n' rows from the table.

  
  SELECT TOP 2 emp_name FROM tutorial_db.employee;
  
  Output:
  emp_name
  ---------
  JAMES
  CLARK
  

• TOP n along with ORDER BY: This will return top 'n' rows after performing sorting(based on ORDER BY clause) on the table data.

  
  SELECT TOP 2 emp_no,salary FROM tutorial_db.employee ORDER BY salary DESC;
  
  Output:
   emp_no   salary
  -------  -------
  1000245  5000.00
  1000288  3000.00
  

➠ TOP n PERCENT: Number of rows to be returned are determined by a formula ((n * number of rows in table) / 100). Value of n can be positive integer or positive decimal.

• Simple TOP n PERCENT: This will return random 'n' percentage of rows from the table.

  
  SELECT TOP 35.5 PERCENT emp_no,salary FROM tutorial_db.employee;
  
  Output:
   emp_no   salary
  -------  -------
  1000294   800.00
  1000262  2450.00
  1000336   950.00
  1000292  3000.00
  1000258  2850.00
  

• TOP n PERCENT along with ORDER BY: This will return top 'n' percentage of rows after performing sorting(based on ORDER BY clause) on the table data.

  
  SELECT TOP 10 PERCENT emp_no,salary FROM tutorial_db.employee ORDER BY salary DESC;
  
  Output:
   emp_no   salary
  -------  -------
  1000245  5000.00
  1000288  3000.00
  

➠ TOP n WITH TIES: This is generally used in places where ranks are determined using TOP n function and there is possibility of ties in the ranked resultset.

SELECT TOP 2 WITH TIES emp_no,salary FROM tutorial_db.employee ORDER BY salary DESC;

Output:
 emp_no   salary
-------  -------
1000245  5000.00
1000288  3000.00
1000292  3000.00

TOP n operator vs QUALIFY RANK / QUALIFY ROW_NUMBER

• TOP n operator along with 'ORDER BY' returns the same results as the QUALIFY clause with the RANK or ROW_NUMBER ordered analytical functions.
• TOP n operator out performs QUALIFY RANK and QUALIFY ROW_NUMBER.
• In best case scenarios, the TOP n operator provides better performance.
• In worst case scenarios, the TOP n operator provides equivalent performance.