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: 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.