ROWNUM
ROWNUM is a pseudo-column and is computed as rows are retrieved.
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, rownum from emp;
ENAME ROWNUM
---------- ----------
SMITH 1
ALLEN 2
WARD 3
JONES 4
MARTIN 5
BLAKE 6
CLARK 7
SCOTT 8
KING 9
TURNER 10
ADAMS 11
11 rows selected.
SQL>
The ROWNUM is limited:
SQL> select ename, rownum from emp order by sal;
ENAME ROWNUM
---------- ----------
SMITH 1
WARD 3
MARTIN 5
TURNER 10
ADAMS 11
ALLEN 2
CLARK 7
BLAKE 6
JONES 4
KING 9
SCOTT 8
11 rows selected.
SQL>
The problem is that the ROWNUM is calculated before the ordering.
To do the proper ranking we can use a inner view.
SQL> select ename, sal, ROWNUM rank from (select ename, sal from emp order by sal);
ENAME SAL RANK
---------- ---------- ----------
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 reverse the ranking with ROWNUM.
SQL> select ename, sal, ROWNUM rank from (select ename, sal from emp order by sal desc);
ENAME SAL RANK
---------- ---------- ----------
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>
To order columns what are not used in ROWNUM:
SQL> select ename,
2 sal,
3 ROWNUM rank from
4 (select ename, sal from emp order by sal desc)
5 order by ename;
ENAME SAL RANK
---------- ---------- ----------
ADAMS 1500 7
ALLEN 1600 6
BLAKE 2850 4
CLARK 2850 5
JONES 2975 3
KING 3000 1
MARTIN 1250 9
SCOTT 3000 2
SMITH 800 11
TURNER 1500 8
WARD 1250 10
11 rows selected.
SQL>
SQL>