This Oracle tutorial explains how to use the Oracle/PLSQL DENSE_RANK function.
The Oracle/PLSQL DENSE_RANK function returns the rank of a row in a group of rows.
DENSE_RANK function calculates row number and does not skip rank if there is a tie.
RANK function can cause non-consecutive rankings if there is a tie. DENSE_RANK function will always result in consecutive rankings.
DENSE_RANK function can be used as an Aggregate function or as an Analytic function.
When used an Aggregate function, the DENSE_RANK function returns the dense rank of a row within a group of rows.
The syntax is as follows:
DENSE_RANK( expression1, ... expression_n ) WITHIN GROUP ( ORDER BY expression1, ... expression_n )
expression1 .. expression_n identify a unique row in the group.
Number of expressions in DENSE_RANK must be the same as in the ORDER BY clause.
The data types must be compatible between the expressions in DENSE_RANK as in the ORDER BY clause.
The following SQL returns the dense rank of an employee with a salary of $1,000 and a bonus of $500.
select DENSE_RANK(1000, 500) WITHIN GROUP (ORDER BY salary, bonus) from emp;
Its general format:
DENSE_RANK over (analytical clause)
analytical clause could be
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER(7, 2),
DEPTNO NUMBER(2));
-- from w ww. j a va 2 s . c o m
INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK', 800, 20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600, 30);
INSERT INTO EMP VALUES (3, 'WARD', 'SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER', 2975, 20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER', 2850, 30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER', 2850, 10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST', 3000, 20);
INSERT INTO EMP VALUES (9, 'KING', 'PRESIDENT',3000, 10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500, 30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK', 1500, 20);
SQL> select ename,
2 sal,
3 DENSE_rank() over (order by sal) toprank from emp;
ENAME SAL TOPRANK
---------- ---------- ----------
SMITH 800 1
WARD 1250 2
MARTIN 1250 2
TURNER 1500 3
ADAMS 1500 3
ALLEN 1600 4
CLARK 2850 5
BLAKE 2850 5
JONES 2975 6
KING 3000 7
SCOTT 3000 7
11 rows selected.
SQL>
The followng code shows how to place NULLS FIRST in DENSE_RANK.
NULLS FIRST is the default.
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER(7, 2),
DEPTNO NUMBER(2));
-- from ww w .java2 s . com
INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK', NULL, 20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', NULL, 30);
INSERT INTO EMP VALUES (3, 'WARD', 'SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER', 2975, 20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER', 2850, 30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER', 2850, 10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST', 3000, 20);
INSERT INTO EMP VALUES (9, 'KING', 'PRESIDENT',3000, 10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500, 30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK', 1500, 20);
SQL> select ename, sal, DENSE_RANK() over (order by sal NULLS FIRST) toprank
2 from emp
3 order by sal;
ENAME SAL TOPRANK
---------- ---------- ----------
MARTIN 1250 2
WARD 1250 2
ADAMS 1500 3
TURNER 1500 3
CLARK 2850 4
BLAKE 2850 4
JONES 2975 5
KING 3000 6
SCOTT 3000 6
SMITH 1
ALLEN 1
11 rows selected.
SQL>
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER(7, 2),
DEPTNO NUMBER(2));
-- from ww w. java 2s . c om
INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK', NULL, 20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', NULL, 30);
INSERT INTO EMP VALUES (3, 'WARD', 'SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER', 2975, 20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER', 2850, 30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER', 2850, 10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST', 3000, 20);
INSERT INTO EMP VALUES (9, 'KING', 'PRESIDENT',3000, 10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500, 30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK', 1500, 20);
SQL> select ename, sal, DENSE_RANK() over (order by sal NULLS LAST) toprank
2 from emp
3 order by sal;
ENAME SAL TOPRANK
---------- ---------- ----------
WARD 1250 1
MARTIN 1250 1
TURNER 1500 2
ADAMS 1500 2
BLAKE 2850 3
CLARK 2850 3
JONES 2975 4
SCOTT 3000 5
KING 3000 5
SMITH 6
ALLEN 6
11 rows selected.
SQL>
-- from ww w.jav a 2s . c o m
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER(7, 2),
DEPTNO NUMBER(2));
INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK', 800, 20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600, 30);
INSERT INTO EMP VALUES (3, 'WARD', 'SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER', 2975, 20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER', 2850, 30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER', 2850, 10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST', 3000, 20);
INSERT INTO EMP VALUES (9, 'KING', 'PRESIDENT',3000, 10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500, 30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK', 1500, 20);
SQL> select * from (
2 select empno, ename, dense_rank() over(order by sal desc) rank from emp
3 )
4 where rank < 3;
EMPNO ENAME RANK
---------- ---------- ----------
8 SCOTT 1
9 KING 1
8 SCOTT 1
8 SCOTT 1
9 KING 1
9 KING 1
4 JONES 2
4 JONES 2
4 JONES 2
9 rows selected.
SQL>
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER(7, 2),
DEPTNO NUMBER(2));
-- w w w .j ava 2s. c o m
INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK', 800, 20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600, 30);
INSERT INTO EMP VALUES (3, 'WARD', 'SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER', 2975, 20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER', 2850, 30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER', 2850, 10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST', 3000, 20);
INSERT INTO EMP VALUES (9, 'KING', 'PRESIDENT',3000, 10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500, 30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK', 1500, 20);
SQL> select job, DENSE_rank() over(order by max(sal)) from emp group by job;
JOB DENSE_RANK()OVER(ORDERBYMAX(SAL))
--------- ---------------------------------
CLERK 1
SALESMAN 2
MANAGER 3
ANALYST 4
PRESIDENT 4
SQL>
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER(7, 2),
NewSAL NUMBER(7, 2),
DEPTNO NUMBER(2));
-- ww w . j av a 2 s. c o m
INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK', 800, 810, 20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600, 1610, 30);
INSERT INTO EMP VALUES (3, 'WARD', 'SALESMAN', 1250, 1210, 30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER', 2975, 2915, 20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250, 1210, 30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER', 2850, 2810, 30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER', 2850, 2810, 10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST', 3000, 3010, 20);
INSERT INTO EMP VALUES (9, 'KING', 'PRESIDENT',3000, 3010, 10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500, 1510, 30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK', 1500, 1510, 20);
SQL>
SQL> select job,
2 RANK() over(order by sal),
3 DENSE_RANK() over(order by sal)
4 from emp;
JOB RANK()OVER(ORDERBYSAL) DENSE_RANK()OVER(ORDERBYSAL)
--------- ---------------------- ----------------------------
CLERK 1 1
SALESMAN 2 2
SALESMAN 2 2
SALESMAN 4 3
CLERK 4 3
SALESMAN 6 4
MANAGER 7 5
MANAGER 7 5
MANAGER 9 6
PRESIDENT 10 7
ANALYST 10 7
11 rows selected.
SQL>
PARTITION BY divides the groups into subgroups.
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER(7, 2),
DEPTNO NUMBER(2));
-- from w w w.j a v a 2s .com
INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK', 800, 20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600, 30);
INSERT INTO EMP VALUES (3, 'WARD', 'SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER', 2975, 20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER', 2850, 30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER', 2850, 10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST', 3000, 20);
INSERT INTO EMP VALUES (9, 'KING', 'PRESIDENT',3000, 10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500, 30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK', 1500, 20);
SQL> SELECT deptno, SUM(sal),
2 DENSE_RANK() OVER (PARTITION BY deptno ORDER BY SUM(sal) DESC) AS rank
3 FROM emp
4 GROUP BY deptno
5 ORDER BY deptno;
DEPTNO SUM(SAL) RANK
---------- ---------- ----------
10 5850 1
20 8275 1
30 8450 1
SQL>
SQL>
The following query uses ROLLUP and DENSE_RANK to get the salary ranks by department.
-- from w ww . jav a 2 s.c o m
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER(7, 2),
DEPTNO NUMBER(2));
INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK', 800, 20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600, 30);
INSERT INTO EMP VALUES (3, 'WARD', 'SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER', 2975, 20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER', 2850, 30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER', 2850, 10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST', 3000, 20);
INSERT INTO EMP VALUES (9, 'KING', 'PRESIDENT',3000, 10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500, 30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK', 1500, 20);
SQL> SELECT deptno, SUM(sal),
2 DENSE_RANK() OVER (ORDER BY SUM(sal) DESC) AS rank
3 FROM emp
4 GROUP BY ROLLUP(deptno)
5 ORDER BY deptno;
DEPTNO SUM(SAL) RANK
---------- ---------- ----------
10 5850 4
20 8275 3
30 8450 2
22575 1
SQL>
The following query uses GROUPING SETS and DENSE_RANK() to get just the salary amount subtotal rankings:
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER(7, 2),
DEPTNO NUMBER(2));
-- www . j av a 2 s. c om
INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK', 800, 20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600, 30);
INSERT INTO EMP VALUES (3, 'WARD', 'SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER', 2975, 20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER', 2850, 30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER', 2850, 10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST', 3000, 20);
INSERT INTO EMP VALUES (9, 'KING', 'PRESIDENT',3000, 10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500, 30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK', 1500, 20);
SQL> SELECT deptno, job, SUM(sal),
2 DENSE_RANK() OVER (ORDER BY SUM(sal) DESC) AS rank
3 FROM emp
4 GROUP BY GROUPING SETS(deptno, job)
5 ORDER BY deptno, job;
DEPTNO JOB SUM(SAL) RANK
---------- --------- ---------- ----------
10 5850 4
20 8275 3
30 8450 2
ANALYST 3000 6
CLERK 2300 7
MANAGER 8675 1
PRESIDENT 3000 6
SALESMAN 5600 5
8 rows selected.
SQL>