0

I am trying to fetch a range of rows in Oracle, but I am only able to fetch from the first row to a row number N.

I cannot do it in case i want to fetch a range in-between the table such as row no. 2 to 5

Lets consider EMP table below that is seen in the sample user SCOTT

Entire Table

Case 1: Fetch a range of rows from 1 to 5, I get the following

select EMPNO, ENAME from (select EMPNO,ENAME from EMP ORDER BY EMPNO)  WHERE ROWNUM BETWEEN 1 AND 5

First 5 Rows

Case 2: Now lets try and fetch 3 rows between 2 and 5. 

select EMPNO, ENAME from (select EMPNO,ENAME from EMP ORDER BY EMPNO)  WHERE ROWNUM BETWEEN 2 AND 5

enter image description here

This gives me an empty Resultset. Why is that? What do I need to change so that I can get a range of rows from somewhere in between the table?

frewper
  • 1,309
  • 6
  • 17
  • 41
  • 1
    As above, as addition you can read [this](http://www.oracle.com/technetwork/issue-archive/2007/07-jan/o17asktom-093877.html) – Seyran Apr 12 '17 at 10:32

2 Answers2

0

You can use RANK function to find the rank of each empno and then filter out the records from 2 to 5.

SELECT *
  FROM (SELECT empno, ename, RANK () OVER (ORDER BY empno) RNK FROM emp)
 WHERE RNK BETWEEN 2 AND 5;
Nitish
  • 1,486
  • 8
  • 19
  • 39
0
select * FROM (SELECT 
   ROW_NUMBER() OVER (ORDER BY EMPNO ) AS ROWID, 
   ENAME
 FROM EMP ) EMP where ROWID between 2 and 5
Mehul Akabari
  • 71
  • 4