RE: Finding out last 10 records

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Wed, 14 Aug 2002 07:43:27 -0800
Message-ID: <F001.004B4908.20020814074327@fatcity.com>

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!

-- 
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 Wed Aug 14 2002 - 10:43:27 CDT