Oracle-L: RE: probem using ROWNUM and ORDER BY clause together

From: Root, Melanie <mroot_at_ip.net>
Date: Thu, 30 May 2002 12:29:54 -0800
Message-ID: <F001.004703D1.20020530122954@fatcity.com>

What happens if you say ----- rownum = 1 ?

Melanie

-----Original Message-----

Sent: Thursday, May 30, 2002 3:08 PM
To: Multiple recipients of list ORACLE-L

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" <ORACLE-L_at_fatcity.com> 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

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

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: Harvinder.Singh_at_MetraTech.com

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

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Tim Gorman
INET: Tim_at_SageLogix.com

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: ListGuru_at_fatcity.com (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: Harvinder.Singh_at_MetraTech.com

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

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