How can I get the last row of a table in an Oracle database?

1153200 pts.
Tags:
Oracle 11g
Oracle Database
I have an Oracle 11g Express database and I'm having trouble getting the last row in one of my tables. Can anybody help me with this? Thanks so much.
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

First you would need to define what “last row” means. You have to define some criteria.

If you are referring the “last inserted” row, the only way would be including a field with the insertion timestamp, or a sequenced number.

When you query a table, you don’t have any guarantee of the order in which Oracle will return the results.  If you need to get the results in a specific order, you have to explicitly tell it, and such order can only be expressed in terms of  table’s columns.

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Brijesh
    Hi,

    Check the query which helps you to find the last row

    SELECT column_name FROM table_name
    ORDER BY column_name DESC
    WHERE ROWNUM <=1;
    14,450 pointsBadges:
    report
  • carlosdl

    The above query is incorrect.


    First: The ORDER BY clause must go after the WHERE clause.  That is a very basic SQL syntax rule.

    Second: Even if the order by clause is moved to the end, the results won't be correct, since the rownum condition is applied before the results ordering.  In a query like that the order by is useless since it will be appiled to a result set of 1 row (and nobody can be sure of what row it will be).

    Third: "last know" needs to be defined (see the answer wiki).

    85,480 pointsBadges:
    report
  • Brijesh
    Can you please try the below query:
    SELECT * FROM tbl WHERE rowid = (SELECT COUNT(rowid) FROM tbl b);

    Note: tbl -> is the table name here

    I just tested it on few tables and it is worked correctly.
    14,450 pointsBadges:
    report
  • carlosdl
    It can't work.  The result of a COUNT will never be equal to a ROWID, because ROWIDs are alphanumerical strings.  That query will always return no rows.

    Replacing the COUNT with a MAX would appear to work, but the returned row won't be the "last" one in the table.

    The ROWID pseudocolumn references the address of a row, nothing more.  Using it for any type of sorting is meaningless.
    85,480 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: