Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2005
    Posts
    116

    Unanswered: Checking last record added into the databse

    helloo..

    How do i check what was the last record added in a certain table if the table doesnt store the date the record was added...

    thx in advance!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You don't/can't.
    There is no such thing as "last record" in any RDBMS, especially when no timestamp exists within the record.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2005
    Posts
    116
    so, there is no way you can determine which was the latest record inserted by the user? it doesnt sound right i think...sure there is a way

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You are free to post a solution, but I won't hold my breath.
    Does the desired solution need to accomodate UPDATES & DELETES?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Feb 2005
    Posts
    116
    got it!

    SELECT * FROM t WHERE ROWid = (SELECT MAX(ROWid) FROM t)

    this query selects the most latest record in a particular table..

    Yeah, i need to use this record for some further analysis

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >SELECT * FROM t WHERE ROWid = (SELECT MAX(ROWid) FROM t)
    >this query selects the most latest record in a particular table..
    The query returns the row with the greatest ROWID; which may or may not the the last row inserted.
    If a row gets deleted whose rowid < MAX(ROWID), then the next insert
    can/may be inserted with that lower value ROWID.
    In this case, the most recently inserted row does NOT qualify for MAX(ROWID).
    While your query may work in many case it is well less that 100% reliable in all cases. If that is good enough for you, then so be it.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Feb 2005
    Posts
    116
    do u hv any better query than this one....
    im afraid as you said, it might not work in some cases...
    so how do i solve this
    thx in advance...
    Last edited by a1jit; 02-26-05 at 23:36.

  8. #8
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    I'm afraid you dont, as anacedent said the database stores no info in the row to indicate time of addition. You best bet if you still want to do it is to add a column or table and then use a trigger to store a timestamp in it. Of course you have to make sure your app doesnt break if you do add a column to the original table.

    Alan

  9. #9
    Join Date
    Feb 2005
    Location
    Leesburg, VA
    Posts
    42
    I have a script I give my students on my personal webspace:

    http://www.orcldba.com/scripts/gendates.sql

    This script will add a create_date and last_update to every table along with a trigger to populate them. We needed the same auditing as you do at my previous job, and this is how we accomplished it.
    Steven Karam
    Oracle 10g Certified Master
    Web: OrclDBA.com
    Email: steve@orcldba.com

  10. #10
    Join Date
    Jun 2003
    Location
    Sydney, Australia
    Posts
    66
    PMJI, I'm new to the "before" functionality of Triggers. My question is: would the trigger in your script capture the time when the Insert statement was executed, or the time the transaction was commited ?

    We recently had an interesting bug, caused by insufficent accuracy in defining what we mean by "LAST". Is it last Inserted or last Committed ?

    We had transactions of uneven length writing to the same table. A reader was polling periodically, attempting to pick up any recent records. ( This was an enque-deque mechanism, developed before Oracle's offering. ) The timestamp was obtained by the program somewhere at the middle of the transaction. If a transaction was running for a longer time, then the record it inserted remained invisible to the reader. Meanwhile, a later record inserted by a shorter transaction became visible earlier. The reader thought it has seen all older records, and missed out on the one created by the long transaction.

    I think this is a classic problem, and would be irrelevant for an auditing application, except perhaps for daytime reporting of recent changes.

Posting Permissions

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