I don't believe any of these responses are valid. ROWID is not an ascending sequential number assigned to the rows as they are created. Rather, it is a composite value that identifies where a row resides, starting with the database segment (if appropriate for the database involved), then the "file number" that Oracle assigns to each physical file, block number, and row number (location) within that block. If space is made available by deleting a row, that space may be reused by a later insert. So, arithmetic with ROWID is not meaningful.
The question is: Exactly what does the questioner mean by "last" row? The last inserted, chronologically? The last to be updated or inserted? The last, in terms of highest/largest primary key?
The concept of "last row" other than by one of those criteria is meaningless. Further, chronologically "last" is simply not available, unless one does rather arcane examinations of the history in Oracle's Redo Log, and that's non-trivial.
Last by primary key -- i.e., largest -- is easy, second last isn't bad. Neither will use its index directly but rather will perform a sort on the key's values to pick the max, then retrieve the row with that value. And so on. For large tables, this can run awhile.
If this is an operation that will be used with some frequency, it will be more efficacious to keep a record of the rowid for last inserted chronologically, or the largest/highest key so far, seperately in its own one-row table and use that. Unfortunately, that breaks as soon as a row is deleted, since it may be the row so identified, and that identification will have to be recomputed should that happen. So, any delete action would have to check for that -- by trying to retrieve the "last" row in PL/SQL and capturing the no-response error, or by keeping the primary key with the rowid in that one row table and comparing it to that of the row being deleted, then trigger the recomputation when it's required. Other methods -- an update based upon an internal query for a count(*) using that saved ROWID or PK value that operates when the returned count(*) is zero -- work as well.
Generally, in an RDB, the concept of a "last" record has little meaning.
select * from (select * from employee order by id asc) where rownum < (select (count(*)-(N - 2)) from employee)
select * from (select * from employee order by id asc) where rownum < (select (count(*)- (N - 1) from employee)
-- FOR FINDING THE NTH ROW
select * from (select * from employee order by id asc) where rownum < N + 1
select * from (select * from employee order by id asc) where rownum < N
I have a table in which each users can enter more than one records.
My task was to find out the last amount entered by each user, i used the following query by using analytical function.
you can also use it for the whole table. I have partitioned it userwise
select UserID,Amount,last_entry_date_time from
select UserID, Amount, entry_date_time,
max(entry_date_time) over (partition by UserID order by USerID) last_entry_date_time
where entry_date_time = last_entry_date_time
order by UserID;