ROWNUM Pseudocolumn

It returns a number indicating the order in which Oracle selects the row from a table or set of joined rows.

Consider the following example.

CREATE TABLE test_rownum(
colA VARCHAR2(10),
colB NUMBER,
colC NUMBER);
INSERT INTO test_rownum
VALUES('a1',100,124);
INSERT INTO test_rownum
VALUES('a2',110,99);
INSERT INTO test_rownum
VALUES('a3',111,211);
INSERT INTO test_rownum
VALUES('a4',109,254);
INSERT INTO test_rownum
VALUES('a5',142,200);
COMMIT;

SELECT *
FROM test_rownum t
WHERE rownum <= 3;

COLA COLB COLC

---------- ---------- ----------

a1 100 124

a2 110 99

a3 111 211

In the above query returns three records from that table.

Lets check another query with the order by conditions.

SELECT *
FROM test_rownum t
WHERE rownum <= 3
ORDER BY t.colc;

COLA COLB COLC

---------- ---------- ----------

a2 110 99

a1 100 124

a3 111 211

In this case the ORDER BY clause is not worked as we expect. In this case ORACLE retrieved three rows and ordered those rows using ‘colC’.

If we want to apply the rownum condition after applying ‘ORDER BY’ condition we need to embedded the ‘ORDER BY’ clause in a subquery.

Following is the rearranged query to retrieve the rows ordered by colC.

SELECT *
FROM (SELECT *
FROM test_rownum
ORDER BY colC)
WHERE rownum <= 3;

COLA COLB COLC

---------- ---------- ----------

a2 110 99

a1 100 124

a5 142 200

You can also use ROWNUM to assign unique values to each row of a table, as in this example:

ALTER TABLE test_rownum
ADD (colD NUMBER);

UPDATE test_rownum
SET colD = rownum;

SELECT *
FROM test_rownum;

COLA COLB COLC COLD

---------- ---------- ---------- --------

a1 100 124 1

a2 110 99 2

a3 111 211 3

a4 109 254 4

a5 142 200 5