ROWNUM pseudo column returns a number from 1 to n, for each row returned by a query, indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.
SELECT ROWNUM, Dept.*
FROM Dept;
ROWNUM DEPTNO DNAME LOC
---------- ---------- -------------- -------------
1 10 ACCOUNTING NEW YORK
2 20 RESEARCH DALLAS
3 30 SALES CHICAGO
4 40 OPERATIONS BOSTON
SELECT ROWNUM, Ename, Deptno, Job
FROM Emp;
ROWNUM ENAME DEPTNO JOB
---------- ---------- ---------- ---------
1 KING 10 PRESIDENT
2 BLAKE 30 MANAGER
3 CLARK 10 MANAGER
4 JONES 20 MANAGER
5 SCOTT 20 ANALYST
6 FORD 20 ANALYST
7 SMITH 20 CLERK
8 ALLEN 30 SALESMAN
9 WARD 30 SALESMAN
10 MARTIN 30 SALESMAN
11 TURNER 30 SALESMAN
12 ADAMS 20 CLERK
13 JAMES 30 CLERK
14 MILLER 10 CLERK
14 rows selected.
ROWNUM pseudo column can be applied with WHERE clause to limit the number of rows. Filters on the ROWNUM will work only with = 1 or < n or <= n ; where n is any +ve number.
ROWNUM never gets allocated unless and until the record has been confirmed for the sake of presentation. ROWNUM internally in the ORACLE architecture is always treated as a constrained state, assigned with an initial value of 1. The value increments each time as the rows are getting selected from the database server.
SELECT ROWNUM, Ename, Deptno, Job
FROM Emp
WHERE ROWNUM = 1;
ROWNUM ENAME DEPTNO JOB
---------- ---------- ---------- ---------
1 KING 10 PRESIDENT
SELECT ROWNUM, Ename, Deptno, Job FROM Emp WHERE ROWNUM = 3; no rows selected
SELECT ROWNUM, Ename, Deptno, Job
FROM Emp
WHERE ROWNUM <> 3;
ROWNUM ENAME DEPTNO JOB
---------- ---------- ---------- ---------
1 KING 10 PRESIDENT
2 BLAKE 30 MANAGER
SELECT ROWNUM, Ename, Deptno, Job
FROM Emp
WHERE ROWNUM <= 5;
ROWNUM ENAME DEPTNO JOB
---------- ---------- ---------- ---------
1 KING 10 PRESIDENT
2 BLAKE 30 MANAGER
3 CLARK 10 MANAGER
4 JONES 20 MANAGER
5 SCOTT 20 ANALYST
SELECT ROWNUM, Ename, Deptno, Job FROM Emp WHERE ROWNUM > 5; no rows selected
SELECT ROWNUM, Ename, Deptno, Job
FROM Emp
WHERE ROWNUM > 0;
ROWNUM ENAME DEPTNO JOB
---------- ---------- ---------- ---------
1 KING 10 PRESIDENT
2 BLAKE 30 MANAGER
3 CLARK 10 MANAGER
4 JONES 20 MANAGER
5 SCOTT 20 ANALYST
6 FORD 20 ANALYST
7 SMITH 20 CLERK
8 ALLEN 30 SALESMAN
9 WARD 30 SALESMAN
10 MARTIN 30 SALESMAN
11 TURNER 30 SALESMAN
12 ADAMS 20 CLERK
13 JAMES 30 CLERK
14 MILLER 10 CLERK
14 rows selected.
SELECT ROWNUM, Ename, Deptno, Job
FROM Emp
WHERE ROWNUM IS NOT NULL;
ROWNUM ENAME DEPTNO JOB
---------- ---------- ---------- ---------
1 KING 10 PRESIDENT
2 BLAKE 30 MANAGER
3 CLARK 10 MANAGER
4 JONES 20 MANAGER
5 SCOTT 20 ANALYST
6 FORD 20 ANALYST
7 SMITH 20 CLERK
8 ALLEN 30 SALESMAN
9 WARD 30 SALESMAN
10 MARTIN 30 SALESMAN
11 TURNER 30 SALESMAN
12 ADAMS 20 CLERK
13 JAMES 30 CLERK
14 MILLER 10 CLERK
14 rows selected.
SELECT ROWNUM, Ename, Deptno, Job
FROM Emp
WHERE ROWNUM IN (1, 2, 5,6);
ROWNUM ENAME DEPTNO JOB
---------- ---------- ---------- ---------
1 KING 10 PRESIDENT
2 BLAKE 30 MANAGER
SELECT ROWNUM, Ename, Deptno, Job
FROM Emp
WHERE ROWNUM BETWEEN 1 AND 5;
ROWNUM ENAME DEPTNO JOB
---------- ---------- ---------- ---------
1 KING 10 PRESIDENT
2 BLAKE 30 MANAGER
3 CLARK 10 MANAGER
4 JONES 20 MANAGER
5 SCOTT 20 ANALYST
SELECT ROWNUM, Ename, Deptno, Job
FROM Emp
WHERE Deptno = 30 AND ROWNUM < 3;
ROWNUM ENAME DEPTNO JOB
---------- ---------- ---------- ---------
1 BLAKE 30 MANAGER
2 ALLEN 30 SALESMAN