Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Posts
    126

    Unanswered: Select last row in db atble

    Is there anyway to return the last row in a table?

    I'm trying to check an import, but there are 22 million rows in this table.

    Thanks!

    Ken

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Select last row in db atble

    Strictly speaking, a table doesn't have a last row. If the table includes a column that orders the rows from first to last in some sense, then you can use that.

    For Oracle, if the table was created by an import as here, then I guess the record with the highest ROWID would be the last one loaded. But that's not true in general.

  3. #3
    Join Date
    Jan 2003
    Posts
    126
    Makes sense!


    I suppose the best I can do is spot check here and there and make sure all the records got imported.

    Thanks!

    Ken

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    In fact, the ROWID doesn't even seem to work for a table freshly created in order:

    SQL> create table t as select * from all_objects
    2 order by object_id
    3 /

    Table created.

    SQL> select max(object_id) from t
    2 /

    MAX(OBJECT_ID)
    --------------
    183644

    SQL> select object_id from t where rowid=(select max(rowid) from t)
    2 /

    OBJECT_ID
    ----------
    113505

  5. #5
    Join Date
    Jan 2003
    Posts
    126
    That is strange isn't it!

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Found this explanation on AskTom:

    Trust me on this one -- there is exactly ONE way to sort of get the last row inserted.
    You MUST have some column that you can order by.

    Rowid - won't work. Its based on flie/block/slot. We reuse rowids, we can even
    change them (partitioned tables, IOTS). You might have extent #1 in file 55 and
    extent #2 in file 2. Extent 4 might start on block 555 of file 3, Extent 5
    might start on block 2 of file 3. Rowids are NOT sortable.
    Even so, I had expected that when inserting rows into a NEW, EMPTY table they would physically go in starting at the first block, first record and so on - apparently not!

Posting Permissions

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