Rownum is always set after the Row has been successfully fetched into
the resultset if u say Rownum < 2 then the first row is fetched and
assigned 1 which is less that 2 so it is taken into the resultset. On
the second fetch rownum is fetched as 2 and since it is not equal 2 the
row is rejected and all other rows following it.
So you get this behaviour.
HTH
Best Regards,
Ganesh R
Tel : +971 (4) 397 3337 Ext 420
Fax : +971 (4) 397 6262
HP : +971 (50) 745 6019
============================================
Live to learn... forget... and learn again.
============================================
-----Original Message-----
From: Harvinder Singh [mailto:Harvinder.[Email Address Removed]
Sent: Thursday, May 30, 2002 9:38 PM
To: LazyDBA.com Discussion
Subject: Problem with ROWNUM
Hi,
Whenever we have to use rownum with order by clause, we used to use
subquery with order by and the use rownum in outer query..in 9i it was
told that we can use order by with rownum without subquery and it works
if we are selecting more than 2 rows like rownum < 3 but it fails when
we do rownum < 2...... did anyone also seen this behaviour or this is
one of another bad day?????....... following is test script from
sqlplus:
SQL> select id_descendent,num_generations from t_account_ancestor inner
SQL> join t_a
ccount on t_account.id_acc=t_account_ancestor.id_descendent where rownum
< 3 and id_acc < 124 order by num_generations desc;
ID_DESCENDENT NUM_GENERATIONS
------------- ---------------
123 1
1 0
SQL> select id_descendent,num_generations from t_account_ancestor inner
SQL> join t_a
ccount on t_account.id_acc=t_account_ancestor.id_descendent where rownum
< 3 and id_acc < 124 order by num_generations asc;
ID_DESCENDENT NUM_GENERATIONS
------------- ---------------
1 0
123 1
SQL> select id_descendent,num_generations from t_account_ancestor inner
SQL> join t_a
ccount on t_account.id_acc=t_account_ancestor.id_descendent where rownum
< 2 and id_acc < 124 order by num_generations asc;
ID_DESCENDENT NUM_GENERATIONS
------------- ---------------
1 0
SQL> select id_descendent,num_generations from t_account_ancestor inner
SQL> join t_a
ccount on t_account.id_acc=t_account_ancestor.id_descendent where rownum
< 2 and id_acc < 124 order by num_generations desc;
ID_DESCENDENT NUM_GENERATIONS
------------- ---------------
1 0
THE RESULT SHOULD BE :
ID_DESCENDENT NUM_GENERATIONS
------------- ---------------
123 1
Thanks
--Harvinder
--------
Oracle documentation is here:
http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
To unsubscribe: send a blank email to oracledba-[Email Address Removed] subscribe: send a blank email to oracledba-[Email Address Removed] the list archive: http://www.LAZYDBA.com/odbareadmail.pl
Tell yer mates about http://www.farAwayJobs.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page