How to do Top-N analysis in Oracle

From oracle8i onwards the inner query can have an order by clause AND IS A REQUIREMENT IN THE SUB QUERY TO PERFORM the Top-N analysis. Top-N queries are useful in scenarios where the need is to display only the n top-most or the n bottom-most records from a table based on a condition. This result set can be used for further analysis.  For example, using Top-N analysis you can perform the following types of queries:

• Top three earners in the company
• Four most recent recruits in the company
• Top two sales reps who have sold the maximum number of products
• Top three products that have had maximum sales in the last six months

The high-level structure of a top-n analysis query is:

select [column_list], ROWNUM [ROWNUM_ALIAS] from (select [column_list] from table ORDER BY Top-N_column) where ROWNUM<=N;

Performing “Top-N” Analysis

Top-N queries use a consistent nested query structure with the elements described below:

1. A subquery or an inline view to generate the sorted list of data. The subquery or the inline view includes the ORDER BY clause to ensure that the ranking is in the desired order. For results retrieving the largest values, a DESC parameter is needed.
2. An outer query to limit the number of rows in the final result set. The outer query includes the following components:

• The ROWNUM pseudocolumn, which assigns a sequential value starting with 1 to each of the rows returned from the subquery.
• A WHERE clause, which specifies the n rows to be returned. The outer WHERE clause must use a < or <= operator.

The following is an example for performing

•  TOP 3 earners names and their salaries.

select ROWNUM as RANK, ename,sal from(select ename,sal from emp ORDER BY sal DESC) WHERE ROWNUM<=3;

      RANK ENAME             SAL

---------- ---------- ----------

         1 KING             5000

         2 SCOTT            3000

         3 FORD             3000

• The least 3 earners names and their salaries.

select ROWNUM as RANK, ename,sal from(select ename,sal from emp ORDER BY sal) WHERE ROWNUM<=3;

      RANK ENAME             SAL

---------- ---------- ----------

         1 SMITH             800

         2 JAMES             950

         3 ADAMS            1100

• To find the top 4 senior most employees in the emp table.

select ROWNUM as SENIORS, SENRS.ename,SENRS.hiredate from (select ename,hiredate from emp order by hiredate)SENRS where ROWNUM<=4;

   SENIORS ENAME      HIREDATE

---------- ---------- ---------

         1 SMITH      17-DEC-80

         2 ALLEN      20-FEB-81

         3 WARD       22-FEB-81

         4 JONES      02-APR-81

• To find 4 junior most employees in the emp table.

select ROWNUM as SENIORS, SENRS.ename,SENRS.hiredate from (select ename,hiredate from emp order by hiredate DESC)SENRS where ROWNUM<=4;

   JUNIORS ENAME      HIREDATE

---------- ---------- ---------

         1 ADAMS      23-MAY-87

         2 SCOTT      19-APR-87

         3 MILLER     23-JAN-82

         4 JAMES      03-DEC-81

SQL>select ROWNUM as SENIORS, SENRS.ename,SENRS.hiredate from (select ename,hiredate from emp order by hiredate)SENRS where ROWNUM<=4 order by rownum desc;

  SENIORS ENAME      HIREDATE

--------- ---------- ---------

        4 JONES      02-APR-81

        3 WARD       22-FEB-81

        2 ALLEN      20-FEB-81

        1 SMITH      17-DEC-80