Hi !


If Oracle recommends the use of rownum dont use it all,
because it gives inconsistent results as rownum is set
on the fetched resultset.


Regards
Vijay K

Harvinder Singh wrote:

> 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 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 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 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 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] To subscribe: send a blank email to oracledba-[Email Address Removed] Visit 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