Oracle SQL :Using ROWNUM in order by clause

                                                  ROWNUMBERING WITH AN ORDER BY CLAUSE

One of the most often uses of the pseudo column rownum is to provide serial numbers to the records in a query. This feature is widely used in reports to represent systematic display of information.
For instance

Listing A
Select rownum, ename, empno
from emp10;

Table A

ROWNUM ENAME          EMPNO
--------- ---------- ---------
        1 KING           7839
        2 BLAKE           7698
        3 CLARK         7782
        4 JONES           7566
        5 MARTIN         7654

However, when we order this statement the rownum gets disturbed as shown below ( Listing B and Table B).

Listing B
select rownum, ename, empno from emp10
order by ename;

Table B
ROWNUM ENAME          EMPNO
--------- ---------- ---------
        2 BLAKE           7698
        3 CLARK           7782
        4 JONES           7566
        1 KING             7839
        5 MARTIN         7654

As we can see from above the employee names did get ordered but the rownum also got the wrong order.  The desired result was BLAKE having rownum 1 , CLARK having a rownum of  2 and so on. To achieve this we have to outer join this table with dual that process forces a implicit order on the rownum as shown below ( Listing C, Table C).

Listing C
select rownum, ename, empno from emp10 a , dual d
where a.ename = d.dummy (+)
order by ename;

Table C
ROWNUM ENAME          EMPNO
------- ---------- ---------
      1         BLAKE         7698
      2 CLARK           7782
      3 JONES           7566
      4 KING             7839
      5         MARTIN          7654

The trick is to do an outer join with the column that you want to order and this process does not disturb the rownum order.  In addition to that if the column is of number datatype  then one should make sure to use TO_CHAR datatype conversion function.