This Oracle tutorial explains how to use the Oracle/PLSQL RANK function.
RANK function calculates row number and skips rank if there is a tie.
Its general format:
RANK over (analytical clause)
analytical clause could be
The Oracle/PLSQL RANK function returns the rank of a value within a group. It is similar to the DENSE_RANK function. However, the rank function can have non-consecutive rankings if there is a tie. DENSE_RANK function will always result in consecutive rankings.
The RANK function can be used two ways - as an Aggregate function or as an Analytic function.
When used as an Aggregate function, the RANK function returns the rank of a row within a group.
The syntax is:
RANK( expression1, ... expression_n ) WITHIN GROUP ( ORDER BY expression1, ... expression_n )
expression1 .. expression_n is one or more expressions which identify a unique row in the group.
There must be the same number of expressions in RANK function as there is in the ORDER BY clause.
The data types IN expressions in RANK function must be compatible with the expressions in the ORDER BY clause.
The following SQL statement returns the rank of an employee with a salary of $1,000 and a bonus of $500 from the employees table.
select RANK(1000, 500) WITHIN GROUP (ORDER BY salary, bonus) from emp;
When used as an Analytic function, the RANK function returns the rank of each row of a query with respective to the other rows.
The syntax is:
rank() OVER ( [ query_partition_clause] ORDER BY clause )
The following SQL statement returns all employees in the Marketing department by using a where clause.
Then it calculates a rank for each unique salary in the Marketing department. If two employees have the same salary, the RANK function would return the same rank for them, which may cause a gap in the ranks or non-consecutive ranks.
select employee_name, salary,
RANK() OVER (PARTITION BY department ORDER BY salary)
from employees
where department = 'Marketing';
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER(7, 2),
DEPTNO NUMBER(2));
-- ww w . ja va 2s . co 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 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>
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 www. j a v a2 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, RANK() over (order by sal NULLS FIRST) toprank
2 from emp
3 order by sal;
ENAME SAL TOPRANK
---------- ---------- ----------
MARTIN 1250 3
WARD 1250 3
ADAMS 1500 5
TURNER 1500 5
CLARK 2850 7
BLAKE 2850 7
JONES 2975 9
KING 3000 10
SCOTT 3000 10
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 w ww .j a v a2 s .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, 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 3
ADAMS 1500 3
BLAKE 2850 5
CLARK 2850 5
JONES 2975 7
SCOTT 3000 8
KING 3000 8
SMITH 10
ALLEN 10
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 w w w.j a v a 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, RANK() over (order by sal NULLS LAST) toprank
2 from emp
3 order by sal NULLS LAST;
ENAME SAL TOPRANK
---------- ---------- ----------
WARD 1250 1
MARTIN 1250 1
TURNER 1500 3
ADAMS 1500 3
BLAKE 2850 5
CLARK 2850 5
JONES 2975 7
SCOTT 3000 8
KING 3000 8
SMITH 10
ALLEN 10
11 rows selected.
SQL>
-- from w ww . j a va 2 s .co 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, rank() over(order by sal desc) rank from emp
3 )
4 where rank < 3;
EMPNO ENAME RANK
---------- ---------- ----------
8 SCOTT 1
9 KING 1
SQL>
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 av a 2s .co 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, rank() over(order by max(sal)) from emp group by job;
JOB RANK()OVER(ORDERBYMAX(SAL))
--------- ---------------------------
CLERK 1
SALESMAN 2
MANAGER 3
ANALYST 4
PRESIDENT 4
SQL>
RANK() and DENSE_RANK() both returns the rank of items in a group.
RANK() leaves a gap for a tie.
DENSE_RANK() doesn't leave a gap for a tie.
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER(7, 2),
DEPTNO NUMBER(2));
-- w w w . ja va2 s. co 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 deptno, SUM(sal),
2 RANK() OVER (ORDER BY SUM(sal) DESC) AS rank,
3 DENSE_RANK() OVER (ORDER BY SUM(sal) DESC) AS dense_rank
4 FROM emp
5 GROUP BY deptno
6 ORDER BY deptno;
DEPTNO SUM(SAL) RANK DENSE_RANK
---------- ---------- ---------- ----------
10 5850 3 3
20 8275 2 2
30 8450 1 1
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 ww . ja v a2 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 deptno, SUM(sal),
2 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 RANK to
get the salary ranks by department.
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER(7, 2),
DEPTNO NUMBER(2));
-- from ww w .j a v a 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 deptno, SUM(sal),
2 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 CUBE and RANK() to get
all rankings of salaries by department and job:
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.ja v a2 s .co 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 deptno, job, SUM(sal),
2 RANK() OVER (ORDER BY SUM(sal) DESC) AS rank
3 FROM emp
4 GROUP BY CUBE(deptno, job)
5 ORDER BY deptno, job;
DEPTNO JOB SUM(SAL) RANK
---------- --------- ---------- ----------
10 MANAGER 2850 13
10 PRESIDENT 3000 8
10 5850 5
20 ANALYST 3000 8
20 CLERK 2300 15
20 MANAGER 2975 12
20 8275 4
30 MANAGER 2850 13
30 SALESMAN 5600 6
30 8450 3
ANALYST 3000 8
CLERK 2300 15
MANAGER 8675 2
PRESIDENT 3000 8
SALESMAN 5600 6
22575 1
16 rows selected.
SQL>
The following query uses GROUPING SETS and 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));
-- w w w . ja v a2 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 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 8
MANAGER 8675 1
PRESIDENT 3000 6
SALESMAN 5600 5
8 rows selected.
SQL>