DENSE_RANK

DENSE_RANK function calculates row number and does not skip rank if there is a tie. Its general format:

DENSE_RANK over (analytical clause)

analytical clause could be

ordering
partitioning
windowing
or the combination of above three


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 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>

Home »
Oracle »
Analytical Functions »

DENSE_RANK: