Rownum

In each row returned through a query, the ROWNUM pseudocolumn returns a number denotes the order in that Oracle retrieves the record. An ROWNUM carried a numeric value. Instance example is,

Example

SELECT deptno,dname,ROWNUM FROM dept;
The above  query  returns  the  department  number  along  with  the  department  name  and  the
ROWNUMber.

The output would be:

DEPTNO    DNAME        ROWNUM
10        ACCOUNTING     1
20        RESEARCH         2
30        SALES               3
40        OPERATIONS     4

Let consider the next example that sorts the display based on the department name.
SELECT deptno,dname,ROWNUM FROM dept ORDER BY dname;
The output for this query will be:

DEPTNO    DNAME        ROWNUM
10            ACCOUNTING    1
40            OPERATIONS    4
20           RESEARCH        2
30              SALES           3

In the above table the display is changed. The Sorting of department name does not result in modifying the row number because the row number is selected as soon as the record is retrieved from the table.

The further example shows condition based retrieval using ROWNUM.
SELECT ename,deptno FROM emp WHERE ROWNUM > 1;
The output of this query will be:

no rows selected

The row number is assigned only after the selection is made. In the query, even before the selection is done, the WHERE condition is executed that results in no records being selected. Thus, the row numbers are not assigned. This leads to the given output.