select rows from third row to n th row in oracle

Question

Possible Duplicate:
Paging with Oracle

I try to select data starting from 11 row. and i used select e_name from copy where rownum>10;

this will not display's anything.. please help me to select 11th row to 15th row in my table

Answer 1

You cannot use rownum like that, you need to wrap everything into a derived table:

select *
from (
  select *,
         rownum as rn
  form your_table
  order by some_column
) 
where rn between 11 and 15

You should use an order by in the inner query because otherwise you will not get consistent results over time. Rows in a relational table do not have any ordering so the database is free to return the rows in any order it feels approriate.

Please read the manual for more details. The reason your query isn't working is documented there with examples.

http://docs.oracle.com/cd/E11882_01/server.112/e26088/pseudocolumns009.htm#i1006297

Answer 2

You have to use like

select e_name
from (select e_name,rownum rno from copy)
where rno > 10 and rno < 16

Sample Example

Answer 3

you could use analytic function row_number() as well. Please consider http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions137.htm

Answer 4

First write the query which will select all the rows like this:-

select ename from employee

Now add a rownum column to your query, rownum will help you to identify the number of row in your query result

select rownum r,ename from employee

Now make your query as a sub query and apply the range on 'r' (rownum)

select * from (selecr rownum r, ename from employee) subq where subq.r between 11 and 15