Re: Finding out last 10 records

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: jkstill_at_cybcon.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).