Oracle SQL - ROWNUM pseudo-column

Introduction

ROWNUM is a pseudo-column and is computed as rows are retrieved.

Since ROWNUM is computed as rows are retrieved, it is somewhat limited.

The ROWNUM numbering takes place before the ordering, i.e., as the rows are retrieved.

ROWNUM operates before the ORDER BY sorting is executed.

Demo

SQL>
SQL>--  ww w  .  j  a  v  a  2s . c  om
SQL> SELECT * FROM emp
  2  ORDER BY orig_salary
  3
SQL> SELECT empno, ename, orig_salary, ROWNUM
  2  FROM emp ORDER BY orig_salary
  3

To fix it

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from  w w w  . jav a  2  s.  com

SQL> create table emp(
  2  empno        Number(3)  NOT NULL,
  3  ename        VARCHAR2(10 BYTE),
  4  hireDate     DATE,
  5  orig_salary  Number(8,2),
  6  curr_salary  Number(8,2),
  7  region       VARCHAR2(1 BYTE)
  8  );
SQL>
SQL> insert into emp values(1,'Alison',DATE '1996-03-21', 45000, NULL,  'E');
SQL> insert into emp values(2,'James',DATE  '1978-12-12', 23000, 32000, 'W');
SQL> insert into emp values(3,'Celia',DATE  '1982-10-24', NULL,  58000, 'E');
SQL> insert into emp values(4,'Robert',DATE '1984-01-15', 31000, NULL,  'W');
SQL> insert into emp values(5,'Linda',DATE  '1987-07-30', NULL,  53000, 'E');
SQL> insert into emp values(6,'David',DATE  '1990-12-31', 78000, NULL,  'W');
SQL> insert into emp values(7,'Jode',DATE  '1996-09-17', 21000, 29000, 'E');
SQL>
SQL> SELECT empno "Emp #",
  2         ename "Name",
  3         orig_salary "Salary",
  4         ROWNUM rank
  5  FROM
  6    (SELECT empno,
  7            ename,
  8            orig_salary
  9     FROM emp
 10     ORDER BY orig_salary) ;

     Emp # Name           Salary       RANK
---------- ---------- ---------- ----------
         7 Jode            21000          1
         2 James           23000          2
         4 Robert          31000          3
         1 Alison          45000          4
         6 David           78000          5
         5 Linda                          6
         3 Celia                          7

7 rows selected.

SQL>
SQL>

The reordering happens to give the same result as the following query without analytical functions:

SELECT empno, ename, os Salary, ROWNUM Toprank 
FROM 
   (SELECT empno, ename, orig_salary os 
   FROM emp 
   ORDER BY orig_salary desc) 
ORDER BY ename 

• Previous
• Next