📋Steps in this guide1/7

RANK and DENSE_RANK Analytic Functions

Simple examples of how to use the RANK and DENSE_RANK analytic functions.

oracle miscconfigurationintermediate

by OracleDba

28 views

Setup

The examples in this article require the following table.

Code/Command (click line numbers to comment):

--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 analytic function is shown below. The analytic clause is described in more detail here . Let's assume we want to assign a sequential order, or rank, to people within a department based on salary, we might use the function like this. 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.

Code/Command (click line numbers to comment):

RANK() OVER ([ query_partition_clause ] order_by_clause)

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>

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 analytic function is shown below. The analytic clause is described in more detail here . The function acts like the function except that it assigns consecutive ranks, so this is not like olympic medaling. As with the 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.

Code/Command (click line numbers to comment):

DENSE_RANK() OVER([ query_partition_clause ] order_by_clause)

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>

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 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 function, and use to indicate is is an aggregate function. The lack of the clause means the whole result set is considered a single group. 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 column to the select list, and including a clause based on .

Code/Command (click line numbers to comment):

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

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

SQL>

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 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 function, and use to indicate is is an aggregate function. The lack of the clause means the whole result set is considered a single group. 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 column to the select list, and including a clause based on .

Code/Command (click line numbers to comment):

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

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

SQL>

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 and was moved to a separate article here .

Quick Links

The "*" indicates the function supports the full analytic syntax, including the windowing clause. For more information see: - Ranking using RANK, DENSE_RANK, ROW_NUMBER - RANK and DENSE_RANK Aggregate Functions - Analytic Functions : All Articles - Analytic Functions - FIRST_VALUE and LAST_VALUE Analytic Functions - RANK - DENSE_RANK Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!