Everyone knows that how to get the first row from a table but what if you want to get the last row and don't know...
how many rows are in the table? This query will solve it!
select * from(select rownum r,tender_master.* from tender_master ) where r in(select count(*) from tender_master) /
Stanley G. writes: The tip would work consistently if records returned in the (select rownum r,tender_master.* from tender_master ) always return in the same order. However, I do not believe this will be the case if Oracle is returning the rows in the order they are physically stored in the database. And since the rows could be reordered by a database reorganization, you could get a different last record even though no records were added or deleted between database reorganizations.
Theo S. writes: I believe that this query is incorrect. It is not true that if you issue a "select * from table;" that the first record you wil see on your screen it the first record in the database. Nor is the record necessarily the last record. There is no way to tell in what order Oracle will fetch the data from disk. What is the last (or first) record? Is that the last record that was inserted in the table or the last record in the last extent of the table?
Bill L. writes: Records added to an Oracle table are added to the end of the table automatically, unless you do something to the table to reorganize it. Future records are then added sequentially to the end of the table. Regardless of this fact, this tip works in locating the last record. Now adjust it slightly and you can find the last record in a sorted manner:
SELECT * FROM (SELECT rownum r,emp.* FROM emp ORDER BY empno) WHERE r in(SELECT count(*) FROM emp) /
Woody B. writes: Since "Last Row" is a pretty fuzzy concept I'll define it as "Last row in a specific sort order". If you need the last row in SQL Server:
Select top 1 <cols> from tablename order by <col> desc
(or asc, depending on your particular sort order.
Rashid A. writes: I am of the opinion that the rownum function gives you the sequential number of the rows retrieved by a query from the Oracle Databse. The correct method I believe is to use the ROWID function as oracle databse assigns the highest number of rowid to a newly inserted record, hence the query should be as below:
Select * from emp where rowid in (Select max(rowid) from emp);
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Hundreds of free Oracle tips and scripts.
- Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
- Best Web Links: Oracle tips, tutorials, and scripts from around the Web.