Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2003
    Posts
    5

    Question Unanswered: Last Record - Oracle 8.1.7

    I need to use a "SELECT" to read the values of the last record in a table.
    Im not able to use any TRIGGER or Autonumeric facility property and the table dont have any value which could be search.
    How can i do it?
    Thanks

  2. #2
    Join Date
    Nov 2002
    Posts
    833
    oracle provides a rowid for each record stored, but it is not recommanded to make use of it

    if is a classifying id consisting of block which is used to store the data within the file, row # within the table and the file# in which the data is stored

    i.e. rowid 000000022.0001.0002

    block 22 row 1 of table fileid 2

  3. #3
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    This may sound strange, but what do you mean by "the last row of the table". Do you mean the last row inserted? Or do you mean the last row that is physically closest to the end of the last file in the tablespace? Or do you mean the last row based on some logical selction criteria?

    For your needs, what is the importance of the last row in the table? Providing you with a usable answer is dependent upon knowing exactly what you are looking for.

    HTH,
    Patrick

  4. #4
    Join Date
    Mar 2003
    Posts
    5
    I need to find "the last row inserted".

  5. #5
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482

    Thumbs up

    You can do that by using the MAX(ROWID). For example:

    SQL> select * from demo;

    no rows selected

    SQL> insert into demo values ('TEST 1');

    1 row created.

    SQL> select * from demo order by ROWID;

    TEST
    ------------------------------
    TEST 1

    SQL> insert into demo values ('TEST 2');

    1 row created.

    SQL> insert into demo values ('TEST 3');

    1 row created.

    SQL> insert into demo values ('TEST 4');

    1 row created.

    SQL> commit;

    Commit complete.

    SQL> select * from demo where ROWID = (select MAX(ROWID) from demo);

    TEST
    ------------------------------
    TEST 4



    Hope that helps,

    clio_usa - OCP - DBA


  6. #6
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    Originally posted by clio_usa
    You can do that by using the MAX(ROWID). For example:

    SQL> select * from demo;

    no rows selected

    SQL> insert into demo values ('TEST 1');

    1 row created.

    SQL> select * from demo order by ROWID;

    TEST
    ------------------------------
    TEST 1

    SQL> insert into demo values ('TEST 2');

    1 row created.

    SQL> insert into demo values ('TEST 3');

    1 row created.

    SQL> insert into demo values ('TEST 4');

    1 row created.

    SQL> commit;

    Commit complete.

    SQL> select * from demo where ROWID = (select MAX(ROWID) from demo);

    TEST
    ------------------------------
    TEST 4



    Hope that helps,

    clio_usa - OCP - DBA

    dbaclick.com

    Clio,
    Will this always work? I thought rowid was based on physical location on disk. There is no gaurantee that the highest rowid is the last record inserted is there? Assume I have a table and happen to delete a bunch of rows 'at the beginning' of the table and those blocks go back on the free list. Eventually, rows will get inserted in those blocks. Won't those blocks have a smaller row id? I'm just wondering, I've never quite grasped the rowid concept I guess.

    Later,
    Patrick

  7. #7
    Join Date
    Mar 2003
    Posts
    5

    Talking

    Thank you very much!!!

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by buckeye234
    Clio,
    Will this always work? I thought rowid was based on physical location on disk. There is no gaurantee that the highest rowid is the last record inserted is there? Assume I have a table and happen to delete a bunch of rows 'at the beginning' of the table and those blocks go back on the free list. Eventually, rows will get inserted in those blocks. Won't those blocks have a smaller row id? I'm just wondering, I've never quite grasped the rowid concept I guess.

    Later,
    Patrick
    Patrick, you are correct:

    SQL> create table t (id number);

    Table created.

    SQL> insert into t select rownum from all_objects;

    23516 rows created.

    SQL> delete t where rownum < 10000;

    9999 rows deleted.

    SQL> insert into t values(987654321);

    1 row created.

    SQL> select id from t
    2 where rowid = (select max(rowid) from t);

    ID
    ----------
    23516

    There is NO MEANINGFUL WAY to get the "last" record inserted into a table unless you have a column like CREATED_DATE DEFAULT SYSDATE (and even then there could be more than one record with the same CREATED_DATE).

  9. #9
    Join Date
    Jan 2003
    Location
    Woking
    Posts
    107
    Originally posted by andrewst
    Patrick, you are correct:

    SQL> create table t (id number);

    Table created.

    SQL> insert into t select rownum from all_objects;

    23516 rows created.

    SQL> delete t where rownum < 10000;

    9999 rows deleted.

    SQL> insert into t values(987654321);

    1 row created.

    SQL> select id from t
    2 where rowid = (select max(rowid) from t);

    ID
    ----------
    23516

    There is NO MEANINGFUL WAY to get the "last" record inserted into a table unless you have a column like CREATED_DATE DEFAULT SYSDATE (and even then there could be more than one record with the same CREATED_DATE).

    Hi,
    I dont know if i am right, but i felt that this is a method of getting the
    last row.

    Ofcourse based on the requirement, the trigger can be made into
    an autonomous transaction.

    Hope it Helps

    CREATE TABLE emp (empno NUMBER(4),
    ename VARCHAR2(10));

    CREATE TABLE emp_log (log_empno NUMBER(4),
    log_ename VARCHAR2(10));

    INSERT INTO emp VALUES (10, 'XXX');
    INSERT INTO emp VALUES (20, 'YYY');
    INSERT INTO emp VALUES (30, 'ZZZ');

    CREATE OR REPLACE
    TRIGGER emp_trig BEFORE INSERT OR UPDATE ON emp
    FOR EACH ROW

    BEGIN
    DELETE emp_log;

    INSERT INTO emp_log
    VALUES (:NEW.empno,
    :NEW.ename);

    EXCEPTION
    WHEN OTHERS THEN
    RAISE;
    END;
    /
    nn

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by NoviceNo1
    Hi,
    I dont know if i am right, but i felt that this is a method of getting the
    last row.

    Ofcourse based on the requirement, the trigger can be made into
    an autonomous transaction.

    Hope it Helps

    CREATE TABLE emp (empno NUMBER(4),
    ename VARCHAR2(10));

    CREATE TABLE emp_log (log_empno NUMBER(4),
    log_ename VARCHAR2(10));

    INSERT INTO emp VALUES (10, 'XXX');
    INSERT INTO emp VALUES (20, 'YYY');
    INSERT INTO emp VALUES (30, 'ZZZ');

    CREATE OR REPLACE
    TRIGGER emp_trig BEFORE INSERT OR UPDATE ON emp
    FOR EACH ROW

    BEGIN
    DELETE emp_log;

    INSERT INTO emp_log
    VALUES (:NEW.empno,
    :NEW.ename);

    EXCEPTION
    WHEN OTHERS THEN
    RAISE;
    END;
    /
    There are problems with that approach. Without autonomous transactions (ATs), it is possible to end up with more than one row in emp_log. With ATs, it is possible to end up with values in emp_log that do not exist in the database, if a ROLLBACK is done after an insert.

    If there really is a requirement to know which record was the last inserted, a timestamp or sequence column should be added to the table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •