ROWNUM pseudo column - PolarDB

ROWNUM is a pseudo column that assigns a unique, incremental integer to each row based on the order rows are retrieved from a query. The first row retrieved gets ROWNUM 1, the second gets ROWNUM 2, and so on.

How it works

ROWNUM values are assigned before sorting. When a query includes an ORDER BY clause, the result set is returned in the specified order, but ROWNUM values reflect the original retrieval order—not the sorted order.

Limit rows returned by a query

Use ROWNUM in the WHERE clause to cap the number of rows a query returns:

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

Output:

 empno | ename |   job
-------+-------+----------
  7369 | SMITH | CLERK
  7499 | ALLEN | SALESMAN
  7521 | WARD  | SALESMAN
  7566 | JONES | MANAGER
(4 rows)

ROWNUM with ORDER BY

Because ROWNUM is assigned before sorting, combining ROWNUM with ORDER BY in the same query level produces unsorted ROWNUM values:

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

Output:

 rownum | empno | ename |   job
--------+-------+-------+----------
      2 |  7499 | ALLEN | SALESMAN
      4 |  7566 | JONES | MANAGER
      1 |  7369 | SMITH | CLERK
      3 |  7521 | WARD  | SALESMAN
(4 rows)

The rows are sorted by ename, but ROWNUM values (2, 4, 1, 3) reflect the original retrieval order.

Assign sequence numbers with ROWNUM

Use ROWNUM in an UPDATE statement to assign sequence numbers to existing rows. The following example adds a seqno column to the jobhist table and populates it:

ALTER TABLE jobhist ADD seqno NUMBER(3);
UPDATE jobhist SET seqno = ROWNUM;

Verify the result:

SELECT seqno, empno, TO_CHAR(startdate,'DD-MON-YY') AS start, job FROM jobhist;

Output:

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