Using the ROWNUM function : ROWNUM « Analytical Functions « Oracle PL/SQL Tutorial






  1. ROWNUM is a pseudo-column and is computed as rows are retrieved.
  2. Since ROWNUM is computed as rows are retrieved, it is somewhat limited.
  3. ROWNUM numbering takes place before the ordering.
SQL>
SQL> -- create demo table
SQL> create table Employee(
  2    EMPNO         NUMBER(3),
  3    ENAME         VARCHAR2(15 BYTE),
  4    HIREDATE      DATE,
  5    ORIG_SALARY   NUMBER(6),
  6    CURR_SALARY   NUMBER(6),
  7    REGION        VARCHAR2(1 BYTE)
  8  )
  9  /

Table created.

SQL>
SQL> -- prepare data
SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_S
ALARY,  REGION)
  2               values (1,      'Jason', to_date('19960725','YYYYMMDD'), 1234,              8767,
        'E')
  3  /

1 row created.

SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_S
ALARY,  REGION)
  2               values (2,      'John',  to_date('19970715','YYYYMMDD'), 2341,              3456,
        'W')
  3  /

1 row created.

SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_S
ALARY,  REGION)
  2               values (3,      'Joe',   to_date('19860125','YYYYMMDD'), 4321,              5654,
        'E')
  3  /

1 row created.

SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_S
ALARY,  REGION)
  2               values (4,      'Tom',   to_date('20060913','YYYYMMDD'), 2413,              6787,
        'W')
  3  /

1 row created.

SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_S
ALARY,  REGION)
  2               values (5,      'Jane',  to_date('20050417','YYYYMMDD'), 7654,              4345,
        'E')
  3  /

1 row created.

SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_S
ALARY,  REGION)
  2               values (6,      'James', to_date('20040718','YYYYMMDD'), 5679,              6546,
        'W')
  3  /

1 row created.

SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_S
ALARY,  REGION)
  2               values (7,      'Jodd',  to_date('20030720','YYYYMMDD'), 5438,              7658,
        'E')
  3  /

1 row created.

SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_S
ALARY,  REGION)
  2               values (8,      'Joke',  to_date('20020101','YYYYMMDD'), 8765,              4543,
        'W')
  3  /

1 row created.

SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_S
ALARY,  REGION)
  2               values (9,      'Jack',  to_date('20010829','YYYYMMDD'), 7896,              1232,
        'E')
  3  /

1 row created.

SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
  2  /

     EMPNO ENAME           HIREDATE  ORIG_SALARY CURR_SALARY R
---------- --------------- --------- ----------- ----------- -
         1 Jason           25-JUL-96        1234        8767 E
         2 John            15-JUL-97        2341        3456 W
         3 Joe             25-JAN-86        4321        5654 E
         4 Tom             13-SEP-06        2413        6787 W
         5 Jane            17-APR-05        7654        4345 E
         6 James           18-JUL-04        5679        6546 W
         7 Jodd            20-JUL-03        5438        7658 E
         8 Joke            01-JAN-02        8765        4543 W
         9 Jack            29-AUG-01        7896        1232 E

9 rows selected.

SQL>
SQL> SELECT empno, ename, orig_salary, ROWNUM
  2  FROM employee ORDER BY orig_salary
  3  /

     EMPNO ENAME           ORIG_SALARY     ROWNUM
---------- --------------- ----------- ----------
         1 Jason                  1234          1
         2 John                   2341          2
         4 Tom                    2413          4
         3 Joe                    4321          3
         7 Jodd                   5438          7
         6 James                  5679          6
         5 Jane                   7654          5
         9 Jack                   7896          9
         8 Joke                   8765          8

9 rows selected.

SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
  2  /

Table dropped.

SQL>








16.2.ROWNUM
16.2.1.Using the ROWNUM function
16.2.2.Depict the rank of the salaries with ROWNUM
16.2.3.To depict the rank of the salaries in descending order with ROWNUM
16.2.4.Desacending order