Home » SQL & PL/SQL » SQL & PL/SQL » How to order by the first created record?
How to order by the first created record? [message #220040] Sun, 18 February 2007 00:51 Go to next message
eshta
Messages: 33
Registered: February 2006
Location: Saudi Arabia
Member

Hi to all,

I have a table with a primary key that is created not in sequence.
This gave me problem when I want to display the records by the first created records.

In other word, I want to view the records order by first created while I didn't record any value that referes to that?

Is there any reserved word that I can use in order by?
Re: How to order by the first created record? [message #220041 is a reply to message #220040] Sun, 18 February 2007 01:12 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
That is only possible if you have a column from which you can determine the order of inserts. Oracle does NOT keep track of that automatically.
Re: How to order by the first created record? [message #220055 is a reply to message #220041] Sun, 18 February 2007 03:45 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Frank wrote on Sun, 18 February 2007 18:12
That is only possible if you have a column from which you can determine the order of inserts. Oracle does NOT keep track of that automatically.


Ooh, ooh!! It's not often you get the chance to gazumph Frank, so when you get a half-gazumph, you gotta take your shot!

How about this?

Havent tried it myself, but if you are using 10g it might work:

SELECT *
FROM tab
ORDER BY ORA_ROWSCN;


Ross Leishman
Re: How to order by the first created record? [message #220058 is a reply to message #220055] Sun, 18 February 2007 05:06 Go to previous messageGo to next message
gintsp
Messages: 118
Registered: February 2007
Senior Member
You have to read also further i.e. what is said about rowdependencies/norowdependencies as well.
From http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7002.htm#CJAEEGDA
ROWDEPENDENCIES Specify ROWDEPENDENCIES if you want to enable row-level dependency tracking. This setting is useful primarily to allow for parallel propagation in replication environments. It increases the size of each row by 6 bytes.

NOROWDEPENDENCIES Specify NOROWDEPENDENCIES if you do not want table to use the row-level dependency tracking feature. This is the default.

Default is NOROWDEPENDENCIES and so the ora_rowscn works only for block level i.e. you cannot track down indiviadual rows.
See example here by Thomas Kyte http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:22948373947565

Gints Plivna
http://www.gplivna.eu

[Updated on: Sun, 18 February 2007 05:07]

Report message to a moderator

Re: How to order by the first created record? [message #220059 is a reply to message #220055] Sun, 18 February 2007 06:19 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I stand gazumphed.

(by the way, there is PLENTY of opportunity imho..)
Re: How to order by the first created record? [message #220092 is a reply to message #220040] Sun, 18 February 2007 18:21 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member
orarowscn along with rowdependencies will help with order of inserts only if no updates ever happend to those rows.

Srini
Re: How to order by the first created record? [message #220134 is a reply to message #220040] Mon, 19 February 2007 01:23 Go to previous messageGo to next message
Smith_X
Messages: 56
Registered: January 2007
Member
Excuse me,

Does ORA_ROWSCN contain date that created, too? If I can track back when this record was create (by order of ORA_ROWSCN), I can use this method to audit/examine something further in future.
icon14.gif  Re: How to order by the first created record? [message #220135 is a reply to message #220040] Mon, 19 February 2007 01:24 Go to previous messageGo to next message
eshta
Messages: 33
Registered: February 2006
Location: Saudi Arabia
Member

Thank you very much for this help.

But it needs to go deep through it

you can use sort by ROWID to sort by first created.

[Updated on: Mon, 19 February 2007 01:24]

Report message to a moderator

Re: How to order by the first created record? [message #220136 is a reply to message #220135] Mon, 19 February 2007 01:36 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
eshta wrote on Mon, 19 February 2007 08:24
you can use sort by ROWID to sort by first created.

No you can't.
Re: How to order by the first created record? [message #220138 is a reply to message #220135] Mon, 19 February 2007 01:40 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
@eshta
I don't think so
By
Vamsi

[Updated on: Mon, 19 February 2007 01:41]

Report message to a moderator

Re: How to order by the first created record? [message #220145 is a reply to message #220135] Mon, 19 February 2007 03:15 Go to previous message
gintsp
Messages: 118
Registered: February 2007
Senior Member
See also this thread:
http://forums.oracle.com/forums/thread.jspa?messageID=1684914#1684914

Gints Plivna
http://www.gplivna.eu
Previous Topic: Multi byte in Oracle
Next Topic: how to retrive password of "system" user
Goto Forum:
  


Current Time: Mon Dec 05 15:14:00 CST 2016

Total time taken to generate the page: 0.10494 seconds