Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Rownum with Dates order by

user613563Jan 18 2009 — edited Jan 18 2009
this is the raw data;
SQL SELECT 
  2  DT             ,
  3  CODE       ,
  4  NO         
  5  FROM TB
  6  WHERE DT BETWEEN TO_DATE('01/01/2000','DD/MM/YYYY') AND TO_DATE('31/12/2009','DD/MM/YYYY')
  7  AND BCODE BETWEEN 'B01' AND 'B01'
  8  AND SCODE BETWEEN 'S07' AND 'S07';

DT        CODE              NO
--------- ------ -------------
21-JAN-08 BUY           11.000
06-JAN-08 BUY           12.000
14-JUL-08 SEL            4.000
15-SEP-08 SEL            5.000
13-JAN-09 SEL            6.000
I want the result to give me rownum and sort it dt wise
i put this query, but saw that row no. 2 of the ROWNUM COL, coming first with the sorted dt wise row.
SQL; SELECT rownum,
  2  DT             ,
  3  CODE       ,
  4  NO         
  5  FROM TB
  6  WHERE DT BETWEEN TO_DATE('01/01/2000','DD/MM/YYYY') AND TO_DATE('31/12/2009','DD/MM/YYYY')
  7  AND   BCODE BETWEEN 'B01' AND 'B01'
  8  AND   SCODE BETWEEN 'S07' AND 'S07'
  9  order by invxh_DATE;

       ROWNUM DT        CODE              NO
------------- --------- ------ -------------
        2.000 06-JAN-08 BUY           12.000
        1.000 21-JAN-08 BUY           11.000
        3.000 14-JUL-08 SEL            4.000
        4.000 15-SEP-08 SEL            5.000
        5.000 13-JAN-09 SEL            6.000
i want rownum to be generetd dt wise, something like;
       ROWNUM DT        CODE              NO
------------- --------- ------ -------------
        1.000 06-JAN-08 BUY           12.000
        2.000 21-JAN-08 BUY           11.000
        3.000 14-JUL-08 SEL            4.000
        4.000 15-SEP-08 SEL            5.000
        5.000 13-JAN-09 SEL            6.000
This post has been answered by 576633 on Jan 18 2009
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 15 2009
Added on Jan 18 2009
2 comments
1,262 views