rownum is best used for plsql blocks where you want to limit the amount of
fetched rows.
BTW
Great for ad-hoc programm to reduce result sets of "wide-angle" queries
before they drill down into the data.

shefea

----- Original Message -----
From: "Vijay Krishnan" <[Email Address Removed] "LazyDBA.com Discussion" <[Email Address Removed] Friday, May 31, 2002 12:48 AM
Subject: Re: Problem with ROWNUM


> 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 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