Re: Order rows

Do you want to physically order them or do you just want to know by time the order in which they were entered.

if the first, no, not that I know of. If the later, yes, add another column (ins_date date) and a trigger to populate that column with sysdate when you insert a row. You can then order by ins_date

|--------+-------------------------------------->

|        |                                      |
|        |                                      |
|        |          "systems_ho/VGIL"@vguard.sat|
|        |          yam.net.in                  |
|        |                                      |
|        |          05/15/2002 02:08 AM         |
|        |          Please respond to ORACLE-L  |
|        |                                      |


|--------+-------------------------------------->

  >----------------------------------------------------|
  |                                                    |
  |       To:     ORACLE-L_at_fatcity.com                 |
  |       cc:     (bcc: Rachel Carmichael)             |
  |       Subject:     Order rows                      |
  >----------------------------------------------------|

Hi All

Is there any method in Oracle to capture or order the rows in a table in the order they were entered.
I tried it with rowid but when a row is deleted, the rowid corresponding to this row is reassigned for a new row
which is inserted into the table at a later stage.

Eg.

SQL> select rowid,abc.* from abc order by rowid;

ROWID                       A
------------------ ----------
AAAFmYAASAAAYsqAAA        100
AAAFmYAASAAAYsqAAB        200
AAAFmYAASAAAYsqAAC        300

I deleted one transaction.

delete from abc where a=200;
commit;

Then I inserted two rows.

insert into abc values(500);
insert into abc values(600);
commit;

Now when I order by rowid

SQL> select rowid,abc.* from abc order by rowid;

ROWID                       A
------------------ ----------
AAAFmYAASAAAYsqAAA        100
AAAFmYAASAAAYsqAAB        600
AAAFmYAASAAAYsqAAC        300
AAAFmYAASAAAYsqAAD        500

I.e The values I entered last appeared second.The rowid (AAAFmYAASAAAYsqAAB ) corresponding to the row I deleted was reassigned for the last entered row (a=600).
What I want is that this must be sorted in the order of its entry. Can anyone help me out.
Thanks in advance
Systems.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
  INET: systems_ho/VGIL_at_vguard.satyam.net.in

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




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