OraFAQ Forum: SQL & PL/SQL » How to order by the first created record?

Home » SQL & PL/SQL » SQL & PL/SQL » How to order by the first created record?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to order by the first created record? [message #220040]

Sun, 18 February 2007 00:51

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?

Report message to a moderator

Re: How to order by the first created record? [message #220041 is a reply to message #220040]

Sun, 18 February 2007 01:12

Frank
Messages: 7901
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.

Report message to a moderator

Re: How to order by the first created record? [message #220055 is a reply to message #220041]

Sun, 18 February 2007 03:45

rleishman
Messages: 3728
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

Report message to a moderator

Re: How to order by the first created record? [message #220058 is a reply to message #220055]

Sun, 18 February 2007 05:06

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

Frank
Messages: 7901
Registered: March 2000

Senior Member

I stand gazumphed.

(by the way, there is PLENTY of opportunity imho..)

Report message to a moderator

Re: How to order by the first created record? [message #220092 is a reply to message #220040]

Sun, 18 February 2007 18:21

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

Report message to a moderator

Re: How to order by the first created record? [message #220134 is a reply to message #220040]

Mon, 19 February 2007 01:23

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.

Report message to a moderator