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

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Thu, 30 May 2002 13:16:39 -0800
Message-ID: <F001.0047057E.20020530131639@fatcity.com>

Harvinder you probably didn't get my point.

SQL doesn't guarantee the order of rows retrieval in absence of ORDER BY clause. To order retrieved rows is the very essence of this clause. In absence of this (ORDER BY) clause, the order of rows retrieval is implementation specific (i.e. each vendor may choose any method they like).

Bottom line, the query you are running is giving you the right results, but to get the results that you WANT, you will have to rewrite the query. Period. No matter how much you try, it is not guaranteed.

Oracle now says that don't depend on the implicit order by performed by group by clause (when order by is not present in the query). I forgot the note id ... I recently read it on Metalink.

Raj

Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art!

-----Original Message-----
Sent: Thursday, May 30, 2002 3:49 PM
To: ORACLE-L_at_fatcity.com
Cc: Jamadagni, Rajendra

Scenario is we have a table having 100 rows and column id_po as unique column with distinct values betweem 1 and 100....... we we try
select id_po from table1 where rownum < 5 order by id_po; it gives result as:
1
2
3
4
and if we try
select id_acc,id_po from table1 where rownum < 5 order by id_po desc; 100
99
98
97

That implies oracle is first getting the result set and then apply order by and then rownum..............

But when we try
select id_acc,id_po from table1 where rownum < 2 order by id_po desc; result is:
1

where it should be 100 if above statement is true........

Thanks
--Harvinder

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jamadagni, Rajendra
  INET: Rajendra.Jamadagni_at_espn.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).



text/plain attachment: ESPN_Disclaimer.txt

Received on Thu May 30 2002 - 16:16:39 CDT