Re: How to do this - SQL

From: Sergei Kuchin <skuchin_at_sprynet.com>
Date: Tue, 06 Oct 1998 00:26:42 -0500
Message-ID: <3619AA12.1E04_at_sprynet.com>


Do not rely on ROWIDs in order to select last inserted rows. For that purpose, usually audit fields are used in tandem with database triggers to populate the fields. If, by any chance, the table has a field, populated from a sequence, then it is okay to use that field for finding rows which were inserted last.

Matt Brennan wrote:
>
> For question #1, try this (I won't swear that it's correct, but I suspect
> it might be, although someone please correct me if I'm wrong):
>
> select dept from tableA where rowid in(select max(rowid) from tableA);
>
> I suspect that the pseudo-column rowid is sequential because when you
> specify no order by clause, the queries seem to return data in the order in
> which it was created/inserted, so I assume rowid is how it does that?
>
> You'd have to test it to be sure, but that's my guess.
> --
> Matt Brennan
> SQL*Tools Specialist
> GERS Retail Systems
> 9725-C Scranton Road
> San Diego, California 92121
> 1-800-854-2263
> mbrennan_at_gers.com
> (Original email address is spam-blocked...please change 'net' to 'com'.)
>
> Norazman Abu Sahir <azhan98_at_tm.net.my> wrote in article
> <01bded98$90809640$c135bcca_at_azhan98.tm.net.my>...
> > Greetings newsgroup member,
> >
> > I have two SQL-ORACLE question,
> >
> > 1)How to write SQL statements that will fetch a certain field
> > of the "last record in the table". Say table A which contains
> > field named Dept. I realized before this in this newsgroup there is
> > a way to get the "last 15" using rownum < 16. However how if I
> > don't know how many records in the table but I just want to get
> > the last one.
> >
> > 2)The 2'nd question is regarding Oracle Report. Just to clarify
> > my understanding. I have a report which have a PL/SQL inside.
> > This PL/SQL was written in one of my function.
> >
> > I can insert a data into my table using sequence. Something like
> > this. INSERT INTO TABLE1 (FIELD1,FIELD2)
> > VALUES(SEQUENCE1.NEXTVAL, 10);
> > It's perfectly OK and insert whatever next value on SEQUENCE1.
> > However when I tried to assign the SEQUENCE1.CURRVAL to one of my
> > variable it generate an error (outside of the DML operation).
> >
> > myvariable := SEQUENCE1.CURRVAL;
> >
> > Because it doesn't recognize SEQUENCE1.
> >
> > How to assign current value of SEQUENCE1 to my variable.Is it possible?
> >
> > Appreciate any help. Excuse me for asking siple question. I just
> another
> > newbie...
> >
> > -NORAZMAN-
> >
> >
> >
> >
> >
> >
> >
Received on Tue Oct 06 1998 - 07:26:42 CEST

Original text of this message