Search code examples
sqloracle-databaseoracle11g

How to get the last row of an Oracle table

I want to get the last row, which I inserted into a table in an Oracle 11g Express database. How can I do this?

Solution

  • There is no such thing as the "last" row in a table, as an Oracle table has no concept of order.

    However, assuming that you wanted to find the last inserted primary key and that this primary key is an incrementing number, you could do something like this:

    select *
  from ( select a.*, max(pk) over () as max_pk
           from my_table a
                )
 where pk = max_pk

    If you have the date that each row was created this would become, if the column is named created:

    select *
  from ( select a.*, max(created) over () as max_created
           from my_table a
                )
 where created = max_created

    Alternatively, you can use an aggregate query, for example:

    select *
  from my_table
 where pk = ( select max(pk) from my_table )

    Here's a little SQL Fiddle to demonstrate.