I want to get the last row, which I inserted into a table in an Oracle 11g Express database. How can I do this?
-
19SELECT * FROM t WHERE id = ( SELECT MAX(id) FROM t )– TerkelCommented Sep 11, 2012 at 21:51
-
1That'll only work if OP's table has id as pk and is an incrementing column. Try "select * from table where rowid in (select max(rowid) from table)"– MichaelNCommented Sep 11, 2012 at 21:55
-
2@MichaelN, rowids are not guaranteed to be inserted in any order.– BenCommented Sep 11, 2012 at 21:58
-
1@ALL - I have a PK with a sequence and trigger to automatically generate row ids.– sky scraperCommented Sep 11, 2012 at 22:00
-
If you've just inserted a row using a sequnce.nextval and are in the same session you could use the sequnce.currval e.g. VARIABLE seq_num NUMBER; EXEC :seq_num := test_seq.CURRVAL; SELECT * FROM test WHERE seq_num = :seq_num;– user672739Commented Feb 1, 2016 at 13:48
9 Answers
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.
-
I got this error when tried it(the middle query) on a table with ~ 3 billion rows ORA-01652: unable to extend temp segment by 128 in tablespace TEMP 01652. 00000 - "unable to extend temp segment by %s in tablespace %s" *Cause: Failed to allocate an extent of the required number of blocks for a temporary segment in the tablespace indicated. *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated. Commented Apr 12, 2016 at 19:36
-
You have a 3bn row table and you're trying to find the last row @Sambit? I can guarantee that you that you don't need to find the last row. Reassess the requirements first. If you really, really, do need to find the last row, you need a way of identifying it uniquely, or you need to increase the amount of sort space (which is the cause of your error)– BenCommented Apr 12, 2016 at 21:15
-
You are right, I tried with a table having 1 billion rows and it worked! Unfortunately I want to find the rowid of the last added row and there is no way I can figure out the last timestamp. However I modified the your query a bit and it worked. Instead of "select a.*, max(created)... " I used "select a.rowid, max(created)..) and it worked for the 3 B table. Commented Apr 12, 2016 at 22:53
-
1I would assume that you're not storing your
MY_ID
in a column with a numeric data-type @vapcguy, a binary sort on strings would explain the behaviour you're seeing. If not it's probably better to ask a new question with a [mvce]. Ping me if you do, I'd be interested to see what the issue is. On rowids, if you only ever do direct path inserts into a unpartitioned heap table, which you never alter in any way (including standard admin), and where you only ever have one data file with free space and never perform any other operation then it's possible that the rowids will be in "ascending"...– BenCommented Aug 13, 2016 at 9:28 -
1Using
currval
would also require you to be in the same session as the last use ofnextval
@Superdooperhero. If there's any chance of multiple sessions writing to a table or of a value getting dropped, i.e. through the failure of a statement, it's best to use the data-driven approach.– BenCommented Jul 7, 2020 at 12:43
SELECT * FROM (
SELECT * FROM table_name ORDER BY sortable_column DESC
) WHERE ROWNUM = 1;
-
will this actually work? i thought
rownum
is applied before andorder by
clause, meaning it'll ignore the sorting you're doing there. oracle.com/technetwork/issue-archive/2006/06-sep/… Commented Sep 16, 2015 at 19:25 -
4@AlexMoore-Niemi The sort in the parens happens first, so rownum works in this example. You will see this further down in the article you linked. Try testing it, and you should see it works.– AcroyearCommented Oct 19, 2015 at 22:46
-
I tried this and got the wrong ID. I have a table I built from another using an
insert into /*+ append */
with anORDER BY DESC
on a primary key ID column. When I built the table originally, it put the rows in the correct order from 1-75. When I run this query or doselect * from ( select a.*, max(pk) over () as max_pk from my_table a ) where pk = max_pk
, I get9
. If I doSELECT ID FROM MyTable WHERE ROWID IN (SELECT MAX(ROWID) FROM MyTable)
, I get the correct ID of75
.– vapcguyCommented Aug 12, 2016 at 14:44 -
@vapcguy you don't have ROWNUM on the queries you have posted. Maybe you commented on the wrong example.– rtaftCommented Aug 12, 2016 at 17:47
-
2@vapcguy which tells me 9 is correct. Your ID's are likely strings and not numbers.– rtaftCommented Aug 15, 2016 at 14:39
select * from table_name ORDER BY primary_id DESC FETCH FIRST 1 ROWS ONLY;
That's the simplest one without doing sub queries
-
This only works on Oracle version 12+, not apply to Oracle 11g– meadlaiCommented Oct 28, 2020 at 5:55
-
1
-
-
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.
-
1No idea, this is correct. The language you use is quite dense though. Don't lose the accuracy, but the more people who understand your answer the better.– BenCommented Jun 13, 2014 at 6:14
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;
-
1The inner select does not guarantee order unless you specify it...the first row could be anything.– rtaftCommented Aug 15, 2016 at 14:35
SELECT * FROM
MY_TABLE
WHERE
<your filters>
ORDER BY PRIMARY_KEY DESC FETCH FIRST ROW ONLY
SELECT /*+ index_desc(t pk_index)*/
pk
FROM TBL t
WHERE ROWNUM = 1;
-
1I recommend that you don't post only code as answer, but also provide an explanation what your code does and how it solves the problem of the question. Answers with an explanation are usually more helpful and of better quality, and are more likely to attract upvotes. Commented May 27, 2023 at 11:23
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
$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"