Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Order rows

RE: Order rows

From: <Rachel_Carmichael_at_Sonymusic.com>
Date: Wed, 15 May 2002 07:33:28 -0800
Message-ID: <F001.004612DD.20020515073328@fatcity.com>

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).
Received on Wed May 15 2002 - 10:33:28 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US