problem is it works if we have rownum < 3(or any value >2) and only
fails if we use rownum < 2...
-----Original Message-----
Sent: Thursday, May 30, 2002 3:34 PM
To: Multiple recipients of list ORACLE-L
it is normal documented behavior.
what you want to do is use an "in-line view" to resolve the issue...
select * from
(select ...
from ...
where ...
order by ...)
where rownum < 2;
Hope this helps...
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, May 30, 2002 12:44 PM
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????
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
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Harvinder Singh
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Tim Gorman
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Harvinder Singh
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
