6.7. ROWNUM

ROWNUM is a pseudocolumn that is assigned an incremental, unique integer value for each row based on the order the rows were retrieved from a query. Therefore, the first row retrieved will have ROWNUM of 1; the second row will have ROWNUM of 2 and so on.

This feature can be used to limit the number of rows retrieved by a query. This is demonstrated in the following example:

6.7.1. Examples

Example

SELECT empno, ename, job 
FROM emp 
WHERE ROWNUM < 5;

Output

 empno | ename  |   job
-------+--------+----------
  7521 | WARD   | SALESMAN
  7566 | JONES  | MANAGER
  7654 | MARTIN | SALESMAN
  7698 | BLAKE  | MANAGER
(4 rows)

Example

The ROWNUM value is assigned to each row before any sorting of the result set takes place. Thus, the result set is returned in the order given by the ORDER BY clause, but the ROWNUM values may not necessarily be in ascending order as shown in the following example:

SELECT ROWNUM, empno, ename, job 
FROM emp 
WHERE ROWNUM < 5 
ORDER BY ename;

Output

 rownum | empno | ename  |   job
--------+-------+--------+----------
      4 |  7698 | BLAKE  | MANAGER
      2 |  7566 | JONES  | MANAGER
      3 |  7654 | MARTIN | SALESMAN
      1 |  7521 | WARD   | SALESMAN
(4 rows)

Example

The following example shows how a sequence number can be added to every row in the jobhist table. First a new column named, seqno, is added to the table and then seqno is set to ROWNUM in the UPDATE command.

ALTER TABLE jobhist ADD seqno NUMBER(3);
UPDATE jobhist SET seqno = ROWNUM;
SELECT seqno, empno, TO_CHAR(startdate,'DD-MON-YY') AS "start", job
FROM jobhist;

The following is the output from the SELECT command showing the new seqno values.

 seqno | empno |   start   |    job
-------+-------+-----------+-----------
     1 |  7521 | 22-FEB-81 | SALESMAN
     2 |  7566 | 02-APR-81 | MANAGER
     3 |  7654 | 28-SEP-81 | SALESMAN
     4 |  7698 | 01-MAY-81 | MANAGER
     5 |  7782 | 09-JUN-81 | MANAGER
     6 |  7788 | 19-APR-87 | CLERK
     7 |  7788 | 13-APR-88 | CLERK
     8 |  7788 | 05-MAY-90 | ANALYST
     9 |  7839 | 17-NOV-81 | PRESIDENT
    10 |  7844 | 08-SEP-81 | SALESMAN
    11 |  7876 | 23-MAY-87 | CLERK
    12 |  7900 | 03-DEC-81 | CLERK
    13 |  7900 | 15-JAN-83 | CLERK
    14 |  7902 | 03-DEC-81 | ANALYST
    15 |  7934 | 23-JAN-82 | CLERK
(15 rows)