Question: How do I find the last row
inserted? I have been trying the following:
select
vehicle_id
from
mytab
where
rowid =(
select
max(rowid)
from
mytab);
Answer: The query you are trying
will return results but not the ones you intend.
The rowid for a
given row is a physical address and in no way indicates the order
of insertion into the table.
The only way to find the last row is to have a
date or timestamp field that is populated by
sysdate at the time of insertion and select the max value of
that column.
The following example shows how to query for the
latest entry, when last_row is populated by sysdate
at the time of insertion:
insert into mytab (vehicle_id, last_row) 123,
sysdate
select
vehicle_id,
max(last_row)
from
mytab
group by
vehicle_id;
Oracle does not insert rows in any particular order, and rows are
inserted based on the next free block in the table's tablespace.
Hence, you need to procedurally mark the "first", "next" or "last"
rows in a table.
Also, see my notes on finding
the top "n" rows
in a table and the
"top 10" rows.
|
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|