RANK

RANK function calculates row number and skips rank if there is a tie. Its general format:

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         rank() over (order by sal) toprank from emp;

ENAME             SAL    TOPRANK
---------- ---------- ----------
SMITH             800          1
WARD             1250          2
MARTIN           1250          2
TURNER           1500          4
ADAMS            1500          4
ALLEN            1600          6
CLARK            2850          7
BLAKE            2850          7
JONES            2975          9
KING             3000         10
SCOTT            3000         10

11 rows selected.

SQL>

Home »
Oracle »
Analytical Functions »

RANK: