probem using ROWNUM and ORDER BY clause together

From: Harvinder Singh <Harvinder.Singh_at_MetraTech.com>
Date: Thu, 30 May 2002 10:44:13 -0800
Message-ID: <F001.0047004E.20020530104413@fatcity.com>

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

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). Received on Thu May 30 2002 - 13:44:13 CDT