CODEWITHSUNDEEP
sqloracle10g
Aspirant
Aspirant

Reputation: 2278

How to retrieve the last 2 records from table?

I have a table with n number of records

How can i retrieve the nth record and (n-1)th record from my table in SQL without using derived table ?

I have tried using ROWID as

select * from table where rowid in (select max(rowid) from table);

It is giving the nth record but i want the (n-1)th record also . And is there any other method other than using max,derived table and pseudo columns

Thanks

Upvotes: 2

Views: 14939

Answers (4)

Lead Baxter
Lead Baxter

Reputation: 43

Assuming rowid as column in your table:

SELECT * FROM table ORDER BY rowid DESC LIMIT 2

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You cannot depend on rowid to get you to the last row in the table. You need an auto-incrementing id or creation time to have the proper ordering.

You can use, for instance:

select *
from (select t.*, row_number() over (order by <id> desc) as seqnum
      from t
     ) t
where seqnum <= 2

Although allowed in the syntax, the order by clause in a subquery is ignored (for instance http://docs.oracle.com/javadb/10.8.2.2/ref/rrefsqlj13658.html).

Just to be clear, rowids have nothing to do with the ordering of rows in a table. The Oracle documentation is quite clear that they specify a physical access path for the data (http://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#i6732). It is true that in an empty database, inserting records into a newtable will probably create a monotonically increasing sequence of row ids. But you cannot depend on this. The only guarantees with rowids are that they are unique within a table and are the fastest way to access a particular row.

I have to admit that I cannot find good documentation on Oracle handling or not handling order by's in subqueries in its most recent versions. ANSI SQL does not require compliant databases to support order by in subqueries. Oracle syntax allows it, and it seems to work in some cases, at least. My best guess is that it would probably work on a single processor, single threaded instance of Oracle, or if the data access is through an index. Once parallelism is introduced, the results would probably not be ordered. Since I started using Oracle (in the mid-1990s), I have been under the impression that order bys in subqueries are generally ignored. My advice would be to not depend on the functionality, until Oracle clearly states that it is supported.

Upvotes: 4

davek
davek

Reputation: 22895

select * from (select * from my_table order by rowid) where rownum <= 2

and for rows between N and M:

select * from (
   select * from (
      select * from my_table order by rowid
                 ) where rownum <= M
              ) where rownum >= N

Upvotes: 2

Matt
Matt

Reputation: 3680

Try this

select top 2 * from table order by rowid desc

Upvotes: 1

Related Questions