Rank and File

Someone asked me the other day how to pull the top-n number of rows from a table.  Of course, I thought of the old way of sorting rows in a subquery and then pulling based on rownum. For example, if you want to show records for the top three salaries, you could code:

Select *
  From ( Select *
           From Emp
          Order By Sal Desc )
  Where RowNum < 4;

RANK is a better option -- and it can be used for a few other neat tricks. There are two variations on RANK syntax: analytical and aggregate.

Type	Basic Syntax	Example
Aggregate	RANK (expr) WITHIN GROUP (Order By )	How does the guy that makes 5000 in salary rank within the group?
Analytic #1	RANK ( )    OVER (Order By )	Rank departments based on total salary in each department.
Analytic #2	RANK ( )    OVER (Partition By      Order By )	Show the rank based on each person's salary within each department.

Aggregate Example:

SQL> break on deptno skip 1
SQL> select * from emp order by deptno, sal; 

Based on the data shown above, if I want to know the rank of the person in department 20 with a salary of 1100, I would code:

Select RANK (              20 ,  1100 ) WITHIN GROUP
            (Order By  DeptNo ,  Sal  ) 
  From Emp;

RANK(20,1100)WITHINGROUP(ORDERBYDEPTNO,SAL)
-------------------------------------------
                                         5

Obviously, all those spaces aren't necessary in the code. I just put them in to show that the values in the 'RANK' line need to match the columns in the 'Order By' line.

Analytic Example #1:

Rank departments based on total salary. (The rank doesn't need to be included in the select.)

Select          RANK() OVER (Order By Sum(Sal) )
     , DeptNo
     , Sum(Sal)
  From Emp
 Group By DeptNo
 Order By       RANK() OVER (Order By Sum(Sal) );

Analytic Example #2:

Show the rank based on each person's salary within each department.

Select DeptNo
     , Sal
     ,       RANK() OVER (Partition By DeptNo 
                              Order By Sal  ) 
       Rank
  From Emp
 Where DeptNo <> 20
 Order By DeptNo, Rank;

A tie will display the same ranking, but then the next ranking value will be skipped.