RE: ROWNUM -- HOW ARE ROWS SELECTED?

Mary Ann,

  Assume that the rows are selected in random order. Primary Keys and Order By exist for ordering and qualifying your data. If you just select * from EMP2, most likely the rows willl come back in the order that they were inserted, but this is not necessarily true, especially if data has been deleted from the table. Add a column that is a sequence number or a date/time stamp, then order by that column.

select emp_sequence_no,gender
from emp
where emp_sequence_no <= 20
order by emp_sequence_no;

Jay Hostetter
Oracle DBA
D. & E. Communications
Ephrata, PA USA

>>> [EMAIL PROTECTED] 07/09/03 12:44PM >>> Its obvious I hadnt fully understood ROWNUM yet, as you see we are learning bits and pieces as we go along.

Help me out here, will you? Talk to me like I'm a 10-year old, its ok.

> MaryAnn, the best way to understand rownum is to do the following:
>
> SQL> SELECT ROWNUM, GENDER
> 2 FROM (SELECT ROWNUM, GENDER
> 3 FROM EMP2
> 4 WHERE ROWNUM <= 20)

> You will quickly see that, no matter how you order the result set,
> the first record returned is rownum #1, second is rownum #2 etc.
> The rownum value is assigned as rows are RETURNED or DISPLAYED,
> not as they are selected.

Fine, the rows are numbered as returned or displayed(not selected).

BUT THEN HOW ARE THEY SELECTED? My emp2 table has 10000+ rows in it, how are only 20 selected? Based on what criteria? How do I get 20 back?

I want to understand this first.

Then, once I get these 20 back, then fine, they are numbered starting from 1, that part I kind of figured it out, or so I think.

The part I dont get, is HOW ARE THEY SELECTED?

thx
maa

**DISCLAIMER
This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of D&E except to the extent that it relates to their official business.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jay Hostetter
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).