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 LazyDBA home page