CODEWITHSUNDEEP
sqloracle-databaseoracle11g
sky scraper
sky scraper

Reputation: 2214

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?

Upvotes: 41

Views: 320311

Answers (9)

Huntr
Huntr

Reputation: 69

Oracle 12.2.0 here,

By ordering by ROWNUM, we can get the last row of a table like that:

SELECT * FROM <TABLE_NAME> ORDER BY ROWNUM DESC FETCH FIRST ROW ONLY

Upvotes: 0

Tigran Shahnazaryan
Tigran Shahnazaryan

Reputation: 46

SELECT /*+ index_desc(t pk_index)*/
    pk
  FROM TBL t
 WHERE ROWNUM = 1;

Upvotes: 0

Random Person
Random Person

Reputation: 49

SELECT * FROM 
  MY_TABLE
WHERE 
  <your filters>
ORDER BY PRIMARY_KEY DESC FETCH FIRST ROW ONLY

Upvotes: 0

haidarvm
haidarvm

Reputation: 630

select * from table_name ORDER BY primary_id DESC FETCH FIRST 1 ROWS ONLY;

That's the simplest one without doing sub queries

Upvotes: 15

lpfx
lpfx

Reputation: 1516

You can do it like this:

SELECT * FROM (SELECT your_table.your_field, versions_starttime
               FROM your_table
               VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE)
WHERE ROWNUM = 1;

Or:

SELECT your_field,ora_rowscn,scn_to_timestamp(ora_rowscn) from your_table WHERE ROWNUM = 1;

Upvotes: 1

rtaft
rtaft

Reputation: 2378

SELECT * FROM (
    SELECT * FROM table_name ORDER BY sortable_column DESC
) WHERE ROWNUM = 1;

Upvotes: 30

rslemos
rslemos

Reputation: 2730

The last row according to a strict total order over composite key K(k1, ..., kn):

SELECT  *
FROM    TableX AS o
WHERE   NOT EXISTS (
            SELECT  *
            FROM    TableX AS i
            WHERE   i.k1 > o.k1
                OR  (i.k1 = o.k1 AND i.k2 > o.k2)
                ...
                OR  (i.k1 = o.k1 AND i.k2 = o.k2 AND i.k3 = o.k3 AND ... AND i.kn > o.kn)
        )
;

Given the special case where K is simple (i.e. not composite), the above is shortened to:

SELECT  *
FROM    TableX AS o
WHERE   NOT EXISTS (
            SELECT  *
            FROM    TableX AS i
            WHERE   i.k1 > o.k1
        )
;

Note that for this query to return just one row the key must order without ties. If ties are allowed, this query will return all the rows tied with the greatest key.

Upvotes: 3

Ben
Ben

Reputation: 52913

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.

Upvotes: 59

melisozmen
melisozmen

Reputation: 11

$sql = "INSERT INTO table_name( field1, field2 )  VALUES ('foo','bar') 
        RETURNING ID INTO :mylastid";
$stmt = oci_parse($db, $sql);
oci_bind_by_name($stmt, "mylastid", $last_id, 8, SQLT_INT);
oci_execute($stmt);

echo "last inserted id is:".$last_id;

Tip: you have to use your id column name in {your_id_col_name} below...

"RETURNING {your_id_col_name} INTO :mylastid"

Upvotes: -1

Related Questions