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!

Row_number function not starting from 1

578548Sep 10 2007 — edited Sep 11 2007
I have two row_number functions in my select statement both has same columns and condition for partition and order by,
except one column in order by which is being ordered by DESC.below is function :

row_number() over (partition by event_cd order by status ASC ,event_date DESC) row1
row_number() over (partition by event_cd order by status DESC ,event_date DESC) row2

Status field has value 0 for incompleted and 1 for completed,if its 0 there will be no date.

The original set of data is :

event_cd status event_date
------------ --------- ---------------
tsk 0
tsk 0
tsk 1 25-aug-2006
tsk 1 28-aug-2006
tsk 1 31-aug-2006
tsk 1 01-sep-2006


Result I am getting for row1 is

status row1
--------- -------

0 1
0 2
1 3
1 4
1 5
1 6

Result for row2 is

status row1
--------- -------

1 5
1 6
1 1
1 2
0 3
0 4

My confusion is why row2 has not its row number starting from 1,it looks like its
depending on row1 results?If yes,then why?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 9 2007
Added on Sep 10 2007
8 comments
2,919 views