RE: Order rows

that works unless there is also a reason to see insert not just in order but by "inserted on a particular date or time". I suppose in that case, add two fields, a date field for the time range and a numeric field for the sequence of insert

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


|        |                       |

|        |                       |

|        |          ktoepke_at_trile|

|        |          giant.com    |

|        |                       |

|        |          05/15/2002   |

|        |          11:13 AM     |

|        |          Please       |

|        |          respond to   |

|        |          ORACLE-L     |

|        |                       |

|--------+----------------------->
  >----------------------------------------------------|
  |                                                    |
  |       To:     ORACLE-L_at_fatcity.com                 |
  |       cc:     (bcc: Rachel Carmichael)             |
  |       Subject:     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).




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