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.
- Setup
- RANK Analytic Function
- DENSE_RANK Analytic Function
- RANK Aggregate Function
- DENSE_RANK Aggregate Function
- FIRST and LAST
- Quick Links
Related articles.
- 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
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.
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...