Row Numbering With an Order By Clause

Posted by: Edi Yanto on: March 7, 2009

• In: Oracle | Programming Concepts
• Comment!

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

SELECT   ROWNUM, rcta.trx_number, rcta.trx_date
FROM ra_customer_trx_all rcta;

ROWNUM    TRX_NUMBER    TRX_DATE
1    40881-05    12/14/2005
2    38384-05    11/24/2005
3    42740-05    12/28/2005
4    42742-05    12/28/2005
5    08883-07    3/12/2007
…………………………………..
…………………………………..
However, when we order this statement the rownum gets disturbed as shown below:

SELECT   ROWNUM, rcta.trx_number, rcta.trx_date
FROM ra_customer_trx_all rcta
ORDER BY rcta.trx_number;

ROWNUM    TRX_NUMBER    TRX_DATE
6427    00001-09    1/2/2009
10827    00001-09CM    1/6/2009
6361    00002-09    1/2/2009
10828    00002-09CM    1/6/2009
6248    00003-09    1/2/2009
……………………………………
……………………………………

As we can see from above the transaction number did get ordered but the rownum also got the wrong order. To achieve this we have to outer join this table with dual that process forces a implicit order on the rownum as shown below.

SELECT   ROWNUM, rcta.trx_number, rcta.trx_date
FROM ra_customer_trx_all rcta, DUAL d
WHERE rcta.trx_number = d.dummy(+)
ORDER BY rcta.trx_number;

ROWNUM    TRX_NUMBER    TRX_DATE
1    00001-09    1/2/2009
2    00001-09CM    1/6/2009
3    00002-09    1/2/2009
4    00002-09CM    1/6/2009
5    00003-09    1/2/2009
………………………………….
…………………………………

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

Tags: query