How to get 10 rows from a table

You can do like this:

select * from <table_name> where rownum <11;

This will give only the first 10 rows from the table.

If you want to get last 10 rows, you can try this:

select * from <table_name> where rownum <11 order by <key field> desc;

This will give the 10 rows from the table in the descending order of the primary key field (or any other field).

But remember one thing that : in oracle records are not placed in the tables in a physical manner, they are placed logically, so that if you want the last 10 records, you can not get them.  You can get the last 10 records from the table only based on some criteria (like the order by clause).

Cheers..
Jagannadh

Oracle 7 Rowid used to be of format

BBBBBBBB.RRRRR.FFFF

Where B was block number, R was Record number within the block
and F was file number.

Oracle 8 has changed rowid format, I don't remember it at the top of
my head. It is documented though.

Using this information, you can in  theory write a code that will show
selected top or bottom rows.

However as Jagannadh already pointed out, request
of showing first or (more particularly) last n rows does raises a question:

"Show relative to what?"

In case, your answer is "some data field", using rownum & ORDER BY CLAUSE is sufficient, easier and maintainable

In case, your answer is "actual physical storage, use information about
ROWID available to us, figure out both bounds. Also, just as a tip, try
sorting by ROWID!

In any case, you may need few subqueries to achieve same using
method.

So, in the end, it is not impossible, just tough!




     

(rownum will give u logical order.)


u can select 10 row in a particular order because the tables are logical,
and i don't think that there is an any need for 10th physical insertion order in table ans u can't do this.


can u explain ur point of selecting 10th row from a table ???

and tell me what version of oracle ur using.

I am assuming a table eg. emp and you want to select 1st 10 rows from emp table

These are the queries

select * from emp where rownum<=10

or

select * from emp where rownum < 11

both will work

And suppose you want to select 10th row from emp table the query is as follows


select * from emp where rowid=
(select rowid from emp                     where rownum <= 10
MINUS
select rowid from emp
where rownum < 10)      

Here is some comments, please include * in the innere select statmt.
Like this...

select sal
 from (select *
              from t_sal
              order by sal desc)
where rownum <=&n)

Brams

Brams, your answer is just like my comment (above).