8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

RANK and DENSE_RANK Analytic Functions

This article gives an overview of the RANK and DENSE_RANK analytic functions. If you are new to analytic functions you should probably read this introduction to analytic functions first.

Related articles.

Setup

The examples in this article require the following table.

--drop table emp purge;

create table emp (
  empno    number(4) constraint pk_emp primary key,
  ename    varchar2(10),
  job      varchar2(9),
  mgr      number(4),
  hiredate date,
  sal      number(7,2),
  comm     number(7,2),
  deptno   number(2)
);

insert into emp values (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,null,20);
insert into emp values (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
insert into emp values (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
insert into emp values (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,null,20);
insert into emp values (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
insert into emp values (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,null,30);
insert into emp values (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,null,10);
insert into emp values (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,null,20);
insert into emp values (7839,'KING','PRESIDENT',null,to_date('17-11-1981','dd-mm-yyyy'),5000,null,10);
insert into emp values (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
insert into emp values (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,null,20);
insert into emp values (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,null,30);
insert into emp values (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,null,20);
insert into emp values (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,null,10);
commit;

RANK Analytic Function

The basic description for the RANK analytic function is shown below. The analytic clause is described in more detail here.

RANK() OVER ([ query_partition_clause ] order_by_clause)

Let's assume we want to assign a sequential order, or rank, to people within a department based on salary, we might use the RANK function like this.

select empno,
       deptno,
       sal,
       rank() over (partition by deptno order by sal) as myrank
from   emp;

     EMPNO     DEPTNO        SAL     MYRANK
---------- ---------- ---------- ----------
      7934         10       1300          1
      7782         10       2450          2
      7839         10       5000          3
      7369         20        800          1
      7876         20       1100          2
      7566         20       2975          3
      7788         20       3000          4
      7902         20       3000          4
      7900         30        950          1
      7654         30       1250          2
      7521         30       1250          2
      7844         30       1500          4
      7499         30       1600          5
      7698         30       2850          6

SQL>

What we see here is where two people have the same salary they are assigned the same rank. When multiple rows share the same rank the next rank in the sequence is not consecutive. This is like olympic medaling in that if two people share the gold, there is no silver medal etc.

The fact we can rank the rows in the department means we are able to do a Top-N query on a per-department basis. The example below assigns the rank in the inline view, then uses that rank to restrict the rows to the bottom 2 (worst paid) employees in each department.

select *
from   (select empno,
               deptno,
               sal,
               rank() over (partition by deptno order by sal) as myrank
        from   emp)
where  myrank <= 2;

     EMPNO     DEPTNO        SAL     MYRANK
---------- ---------- ---------- ----------
      7934         10       1300          1
      7782         10       2450          2
      7369         20        800          1
      7876         20       1100          2
      7900         30        950          1
      7521         30       1250          2
      7654         30       1250          2

SQL>

DENSE_RANK Analytic Function

The basic description for the DENSE_RANK analytic function is shown below. The analytic clause is described in more detail here.

DENSE_RANK() OVER([ query_partition_clause ] order_by_clause)

The DENSE_RANK function acts like the RANK function except that it assigns consecutive ranks, so this is not like olympic medaling.

select empno,
       deptno,
       sal,
       dense_rank() over (partition by deptno order by sal) as myrank
from   emp;

     EMPNO     DEPTNO        SAL     MYRANK
---------- ---------- ---------- ----------
      7934         10       1300          1
      7782         10       2450          2
      7839         10       5000          3
      7369         20        800          1
      7876         20       1100          2
      7566         20       2975          3
      7788         20       3000          4
      7902         20       3000          4
      7900         30        950          1
      7654         30       1250          2
      7521         30       1250          2
      7844         30       1500          3
      7499         30       1600          4
      7698         30       2850          5

SQL>

As with the RANK analytic function, we can do a Top-N query on a per-department basis. The example below assigns the dense rank in the inline view, then uses that rank to restrict the rows to the top 2 (best paid) employees in each department.

select *
from   (select empno,
               deptno,
               sal,
               dense_rank() over (partition by deptno order by sal desc) as myrank
        from   emp)
where  myrank <= 2;

     EMPNO     DEPTNO        SAL     MYRANK
---------- ---------- ---------- ----------
      7839         10       5000          1
      7782         10       2450          2
      7788         20       3000          1
      7902         20       3000          1
      7566         20       2975          2
      7698         30       2850          1
      7499         30       1600          2

SQL>

RANK Aggregate Function

The RANK function can also be used as an aggregate function to predict the ranking of a new value in an existing result set. Lets assume our salary is 2000, and we want to know where we would rank in the company from lowest to highest. We would use 2000 as a parameter to the RANK function, and use WITHIN GROUP to indicate is is an aggregate function. The lack of the GROUP BY clause means the whole result set is considered a single group.

select rank(2000) within group (order by sal) as row_rank
from   emp;

  ROW_RANK
----------
         9

SQL>

We can see a salary of 2000 would put us at rank 9 in list of lowest to highest salaries.

We can find our rank within a department by adding the DEPTNO column to the select list, and including a GROUP BY clause based on DEPTNO.

select deptno,
       rank(2000) within group (order by sal) as row_rank
from   emp
group by deptno
order by deptno;

    DEPTNO   ROW_RANK
---------- ----------
        10          2
        20          3
        30          6

SQL>

DENSE_RANK Aggregate Function

The DENSE_RANK function can also be used as an aggregate function to predict the ranking of a new value in an existing result set. Lets assume our salary is 2000, and we want to know where we would rank in the company from lowest to highest. We would use 2000 as a parameter to the DESNE_RANK function, and use WITHIN GROUP to indicate is is an aggregate function. The lack of the GROUP BY clause means the whole result set is considered a single group.

select dense_rank(2000) within group (order by sal) as row_rank
from   emp;

  ROW_RANK
----------
         8

SQL>

We can see a salary of 2000 would put us at rank 8 in list of lowest to highest salaries.

We can find our rank within a department by adding the DEPTNO column to the select list, and including a GROUP BY clause based on DEPTNO.

select deptno,
       dense_rank(2000) within group (order by sal) as row_rank
from   emp
group by deptno
order by deptno;

    DEPTNO   ROW_RANK
---------- ----------
        10          2
        20          3
        30          5

SQL>

FIRST and LAST

The information about using ranking with FIRST and LAST was moved to a separate article here.

Quick Links

The "*" indicates the function supports the full analytic syntax, including the windowing clause.

AVG * BIT_AND_AGG * BIT_OR_AGG * BIT_XOR_AGG * CHECKSUM *
CLUSTER_DETAILS CLUSTER_DISTANCE CLUSTER_ID CLUSTER_SET CORR *
COUNT * COVAR_POP * COVAR_SAMP * CUME_DIST DENSE_RANK
FEATURE_DETAILS FEATURE_ID FEATURE_SET FEATURE_VALUE FIRST
FIRST_VALUE * KURTOSIS_POP * KURTOSIS_SAMP * LAG LAST
LAST_VALUE * LEAD LISTAGG MATCH_RECOGNIZE MAX *
MEDIAN MIN * NTH_VALUE * NTILE PERCENT_RANK
PERCENTILE_CONT PERCENTILE_DISC PREDICTION PREDICTION_COST PREDICTION
PREDICTION_COST PREDICTION_DETAILS PREDICTION_PROBABILITY PREDICTION_SET RANK
RATIO_TO_REPORT REGR_ (Linear Regression) Functions * ROW_NUMBER SKEWNESS_POP * SKEWNESS_SAMP *
STDDEV * STDDEV_POP * STDDEV_SAMP * SUM * VAR_POP *
VAR_SAMP * VARIANCE * String Aggregation Top-N Queries  

For more information see:

Hope this helps. Regards Tim...

Back to the Top.