ROW_NUMBER function deals with the row numbering for ranking.
Its general format:
ROW_NUMBER 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));
-- ww w. 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 ename, sal, row_number() over (order by sal) toprank from emp;
ENAME SAL TOPRANK
---------- ---------- ----------
SMITH 800 1
WARD 1250 2
MARTIN 1250 3
TURNER 1500 4
ADAMS 1500 5
ALLEN 1600 6
CLARK 2850 7
BLAKE 2850 8
JONES 2975 9
KING 3000 10
SCOTT 3000 11
11 rows selected.
SQL>
It is better to add the order by clause to the sql itself since different ordering may be used for different analytical functions.
SQL> select ename, sal, row_number() over (order by sal) toprank
2 from emp-- from www .ja va2 s . c o m
3 order by sal;
ENAME SAL TOPRANK
---------- ---------- ----------
SMITH 800 1
WARD 1250 2
MARTIN 1250 3
TURNER 1500 4
ADAMS 1500 5
ALLEN 1600 6
CLARK 2850 7
BLAKE 2850 8
JONES 2975 9
KING 3000 10
SCOTT 3000 11
11 rows selected.
SQL>
We can also do the reverse ordering:
SQL> select ename, sal, row_number() over (order by sal desc) toprank
2 from emp-- from w w w. j a v a 2s. com
3 order by sal desc;
ENAME SAL TOPRANK
---------- ---------- ----------
KING 3000 1
SCOTT 3000 2
JONES 2975 3
BLAKE 2850 4
CLARK 2850 5
ALLEN 1600 6
ADAMS 1500 7
TURNER 1500 8
MARTIN 1250 9
WARD 1250 10
SMITH 800 11
11 rows selected.
SQL>
We can also reorder on some column other than the column used in row_number function.
SQL> select ename, sal, row_number() over (order by sal desc) toprank
2 from emp-- from w w w . j av a 2 s . c o m
3 order by sal, JOB;
ENAME SAL TOPRANK
---------- ---------- ----------
SMITH 800 11
WARD 1250 10
MARTIN 1250 9
ADAMS 1500 7
TURNER 1500 8
ALLEN 1600 6
BLAKE 2850 4
CLARK 2850 5
JONES 2975 3
SCOTT 3000 2
KING 3000 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));
-- ww w .j a v a 2 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 * from (
2 select empno, ename, row_number() over(order by sal desc) rank from emp
3 )
4 where rank < 3;
EMPNO ENAME RANK
---------- ---------- ----------
8 SCOTT 1
9 KING 2
SQL>
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER(7, 2),
DEPTNO NUMBER(2));
-- w ww . j ava 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, row_number() over (order by sal) toprank
2 from emp
3 order by sal;
ENAME SAL TOPRANK
---------- ---------- ----------
WARD 1250 1
MARTIN 1250 2
TURNER 1500 3
ADAMS 1500 4
BLAKE 2850 5
CLARK 2850 6
JONES 2975 7
SCOTT 3000 8
KING 3000 9
SMITH 10
ALLEN 11
11 rows selected.
SQL>
In descending order:
SQL> select ename, sal, row_number() over (order by sal desc) toprank
2 from emp-- w ww . ja v a 2 s . c o m
3 order by sal desc;
ENAME SAL TOPRANK
---------- ---------- ----------
SMITH 1
ALLEN 2
KING 3000 3
SCOTT 3000 4
JONES 2975 5
BLAKE 2850 6
CLARK 2850 7
TURNER 1500 8
ADAMS 1500 9
MARTIN 1250 10
WARD 1250 11
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 a2s. c o m
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, row_number() over (order by sal NULLS FIRST) toprank
2 from emp
3 order by sal;
ENAME SAL TOPRANK
---------- ---------- ----------
MARTIN 1250 4
WARD 1250 3
ADAMS 1500 6
TURNER 1500 5
CLARK 2850 7
BLAKE 2850 8
JONES 2975 9
KING 3000 10
SCOTT 3000 11
SMITH 1
ALLEN 2
11 rows selected.
SQL>
NULL 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 w w w . j a va 2 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, row_number() over (order by sal NULLS LAST) toprank
2 from emp
3 order by sal;
ENAME SAL TOPRANK
---------- ---------- ----------
WARD 1250 1
MARTIN 1250 2
TURNER 1500 3
ADAMS 1500 4
BLAKE 2850 5
CLARK 2850 6
JONES 2975 7
SCOTT 3000 8
KING 3000 9
SMITH 10
ALLEN 11
11 rows selected.
SQL>
NULL 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));
-- w w w . 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 job, ROW_NUMBER() over(order by max(sal)) from emp group by job;
JOB ROW_NUMBER()OVER(ORDERBYMAX(SAL))
--------- ---------------------------------
CLERK 1
SALESMAN 2
MANAGER 3
ANALYST 4
PRESIDENT 5
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 av a 2 s .c o m
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>
SQL> select empno, ename, sal, deptno, row_number() over(partition by deptno ORDER by sal)
2 from emp
3 order by deptno;
EMPNO ENAME SAL DEPTNO ROW_NUMBER()OVER(PARTITIONBYDEPTNOORDERBYSAL)
---------- ---------- ---------- -------------------------------------------------------
7 CLARK 2850 10 1
9 KING 3000 10 2
11 ADAMS 1500 20 1
4 JONES 2975 20 2
8 SCOTT 3000 20 3
1 SMITH 20 4
3 WARD 1250 30 1
5 MARTIN 1250 30 2
10 TURNER 1500 30 3
6 BLAKE 2850 30 4
2 ALLEN 30 5
11 rows selected.
SQL>