Menu
I want to get the last row, which I inserted into a table in an Oracle 11g Express database. How can I do this?
770
To get the last record, the following is the query. mysql> select *from getLastRecord ORDER BY id DESC LIMIT 1; The following is the output. The above output shows that we have fetched the last record, with Id 4 and Name Carol.
You could use ORA_ROWSCN to get the last scn for each block on the table as an *approximate* means of seeing when things happened, or the flashback query syntax to get transaction information, but you are limited to the undo_retention settings on your database.
Records are not returned in order of anything. and rownum = 1; will get the "last" record. It is the ONLY way.
We can use the ORDER BY statement and LIMT clause to extract the last data. The basic idea is to sort the sort the table in descending order and then we will limit the number of rows to 1. In this way, we will get the output as the last row of the table. And then we can select the entry which we want to retrieve.
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.
145
SELECT * FROM (
SELECT * FROM table_name ORDER BY sortable_column DESC
) WHERE ROWNUM = 1;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
