RE: Order rows

using a date will work unless you get multiple records created in a given second. Use a sequence generated number. The larger the number, the newer the record. Just order by the sequence to see the order the records were inserted.

Caver

-----Original Message-----
[mailto:Rachel_Carmichael_at_Sonymusic.com] Sent: Wednesday, May 15, 2002 10:03 AM
To: Multiple recipients of list ORACLE-L

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