Query for selecting the last row

[rlaknar]

I am looking for query to select the last row of a table. Please help.

[mir77]

HI,
You can find your tables last/or first row by creating a query of in line view as:

select b.emp_id,b.last_name,b.first_name
from (select rownum,emp_id,last_name,first_name
         from employee
         order by emp_id desc) b
where rownum=1;

OR
if you wanna to select the last row of a table by inserting time then write as below:

select * from employee
where rowid=(select max(rowid) from employee);

thank you

[debasisdas]

try the sample code

select * from branch
minus
select * from branch where rownum < (select count(*) from branch)

[ranjit07]

i want to know how to retrive 2nd or 3rd last row from table

[ranjit07]

how delete the row in hibernate without using primary key?

[pavan_teja]

i want to know how to retrive 2nd or 3rd last row from table

This is very easy indeed....

Select * from employee
where rowid=((SELECT * FROM employee WHERE rowid=(SELECT max(rowid) FROM employee))-1)

to selct 2nd last row in a employee table.

Select * from employee
where rowid=((SELECT * FROM employee WHERE rowid=(SELECT max(rowid) FROM employee))-2)

to selct 3rd last row in a employee table.

[pavan_teja]

i want to know how to retrive 2nd or 3rd last row from table

You can also do it this way....

To get the 2nd last row from a table emp, use

QUERY1:
select * from (select * from emp
minus
select * from emp where rownum < (select (count(*)-1) from emp)) where rownum=1

To get the 3rd last row from a table emp, use

QUERY2:
select * from (select * from emp
minus
select * from emp where rownum < (select (count(*)-2) from emp)) where rownum=1

Get back to me if any issues..

[NeverLift]

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.

[XcessFiber]

-- FOR FINDING NTH last ROW

select * from (select * from employee order by id asc) where rownum < (select (count(*)-(N - 2)) from employee)
minus
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
minus
select * from (select * from employee order by id asc) where rownum < N

==

You're Welcome !!

[NeverLift]

That is so cool!!

[arshadshabbir]

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
   from users_detail
)
where entry_date_time = last_entry_date_time
order by UserID;