Document Sample
rn Powered By Docstoc
					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 --------- ---------- --------2 BLAKE 3 CLARK 4 JONES 1 KING 5 MARTIN

EMPNO 7698 7782 7566 7839 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 ------- ---------1 BLAKE 2 CLARK 3 JONES 4 KING 5 MARTIN

EMPNO --------7698 7782 7566 7839 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.

Shared By: