ROW_NUMBER
ROW_NUMBER function deals with the row numbering for ranking.
Its general format:
ROW_NUMBER 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, 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
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
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
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>