SQL | Top-N Queries

Top-N Analysis in SQL deals with How to limit the number of rows returned from ordered sets of data in SQL. 
Top-N queries ask for the n smallest or largest values of a column. Both smallest and largest values sets are considered Top-N queries. Following this type of searching technique could save lot of time and complexities. Top-N analysis are useful in cases where the need is to display only the n bottom-most or the n top- 
most records from a table based on a condition. This result set can be used for further analysis. 

For example, using Top-N analysis we can perform the following types of queries:

• The top five products that have had the maximum sales in the last two months.
• The top three agents who sold the maximum policies.
• The least two students who scored minimum marks in end semester exams. 
  

Performing Top-N Analysis

Following the below mentioned queries we can easily understand the working of Top-N analysis in SQL: 
Syntax:

SELECT [column_list], ROWNUM
FROM (SELECT [column_list]
      FROM table_name
      ORDER BY Top-N_clolumn)
WHERE ROWNUM<=N;

We will perform the various commands on the following table named Employee:

Example 1:

Input : 
SELECT ROWNUM as RANK, first_name, last_name, employee_id, salary
FROM (SELECT salary, first_name, last_name, employee_id
      FROM Employee
      ORDER BY salary)
WHERE ROWNUM<=3;

Output :

Explanation: In the above SQL statement, the required fields are displayed for employees with top 3 lowest salaries. The result is displayed in increasing order of their salaries. 

Example 2:

Input : 
SELECT ROWNUM as RANK, first_name, employee_id, hire_date
FROM (SELECT first_name, employee_id, hire_date
      FROM Employee
      ORDER BY hire_date)
WHERE ROWNUM<=3;

Output :

Explanation: In the above SQL statement, the required fields are displayed for those 3 employees who were hired earliest. The result is displayed in increasing order of their hire date. 

Different styles for using Top-N analysis

• Inline View and ROWNUM : The classic Top-N style query uses an ordered inline view to force the data into the correct order which then finally uses the ROWNUM check to limit the data returned. 

  Example:
  

Input : 
SELECT first_name, last_name
FROM (SELECT first_name, last_name
      FROM Employee
      ORDER BY salary DESC)
WHERE ROWNUM<=4;

• Output :
  

• Explanation: In the above SQL statement, the required fields are displayed for highest paid 4 employees. The altering is done by ORDER BY clause.
• Nested Inline View and ROWNUM : This method can also be used for paging through data, like paged web reports. 
  Example:
  

Input :
SELECT employee_id, first_name, salary
FROM   (SELECT employee_id, first_name, salary, rownum AS rnum
        FROM   (SELECT employee_id, first_name, salary
                FROM Employee
                ORDER BY salary)
        WHERE rownum<=4)
WHERE  rnum>=2;

• Output :
  

• Explanation: In the above SQL statement, first of all the inside query runs and gives its output to the outer query which then finally gives us the desired output.
• Using RANK function : The RANK analytic function assigns a sequential rank to each distinct value in output. 
  Example:
  

Input : 
SELECT dpartment_id, first_name
FROM (SELECT dpartment_id, first_name,
      RANK() OVER (ORDER BY dpartment_id DESC) AS rnum 
      FROM Employee)
WHERE rnum<=3;

• Output :
  

• Explanation: In the above SQL statement, RANK() function also acts as a virtual field whose value is restricted at the end. RANK() function doesn’t give us the top N rows or the top N distinct values. The number of rows returned is dependent on the number of duplicates in the data.
• Using DENSE_RANK function : The DENSE_RANK analytic function is similar to RANK() function. The difference is that the ranks are compacted due to which there are no gaps. 
  Example:
  

Input : 
SELECT dpartment_id, first_name
FROM (SELECT dpartment_id, first_name,
      DENSE_RANK() OVER (ORDER BY dpartment_id DESC) AS rnum 
      FROM Employee)
WHERE rnum<=3;

• Output :
  

• Explanation: In the above SQL statement, DENSE_RANK() function also assigns same rank to the duplicate values but there is no gap in the rank sequence. Thus it always gives us a Top N distinct values result.
• Using ROW_NUMBER function : The ROW_NUMBER analytic function is similar to ROWNUM virtual column but like all analytic functions its action can be limited to a specific output of data based on the order of data. 
  Example:
  

Input : 
SELECT dpartment_id, first_name
FROM (SELECT dpartment_id, first_name,
      ROW_NUMBER() OVER (ORDER BY dpartment_id DESC) AS rnum 
      FROM Employee)
WHERE rnum<=4;

• Output :
  

• Explanation: In the above SQL statement, ROW_NUMBER() will only select the top N values irrespective of their being duplicate.