Rownum is not necessarily useless for this kind of query. In versions of Oracle (8i+) where an inline view can include an order by, it can be rather useful.
Top 5 salaries: select ename, sal from ( select ename, sal from scott.emp order by sal desc ) a where rownum < 6 Jared On Wednesday 14 August 2002 08:43, Jamadagni, Rajendra wrote: > This email just tells us that such questions need to be included in FAQ ... > > Bottom line is > * No matter how you use ROWNUM, it is USELESS. > * No matter how you use ROWID, it is USELESS. > * If you don't have a "PK" or "any type of unique incrementing key" or "a > timestamp with a key" it won't help you. > * What the He$$ is 'Last Rows' and 'First Rows'? > * First rows as in > * first 10 rows by employee salary? > * first 10 rows by employee lastname? > * first 10 rows by employee first? > * first 10 rows by employee birth date? > * first 10 rows by employee date of joining by work? > > You see, unless you "define" I mean "clearly define" what do you mean by > last and first, NOTHING will help you. We as humans distinctively turn to > chronological order when someone tells last or first. But is that what the > original poster wants? The original poster himself is not clear on the > requirements so any number of solution will not help. > > Santosh, this has been repeated many times, but here it is once again ... > > 'SQL by itself will NOT return rows in any particular order, because > default order is "implementation dependent".' In other words Oracle can > return that data in any damn order it pleases. That's precisely why the > wise men who developed SQL standards gave us 'ORDER BY' clause. > > Now, ORDER BY clause must be used with (one or more) parameter(s) i.e. a > column or expression. This column or expression will help you determine > your first and last requirements. > > Timestamp alone is not sufficient in case of chronological order. What if > my system inserts 25 rows in less than one second, how would you determine > the order then? > > There .... now I feel better ... I think this should be one of those 10 > commandments for developers .. I am still surprised by number of replies > that include rownum and rowid without ORDER BY ... > > 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! ---------------------------------------- Content-Type: text/plain; charset="iso-8859-1"; name="ESPN_Disclaimer.txt" Content-Transfer-Encoding: 7bit Content-Description: ---------------------------------------- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still 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).
