select rows between two values in Oracle 11g

Question

This is a common question I saw in many places, but don't know yet it possible or not. I'm trying to select rows between 2 and 5 in following way using oracle sql developer tool.

As of result this query, this should select 3rd and 4th query according to below query

SELECT * FROM MyTable
WHERE ROWNUM > 2 AND ROWNUM < 5

but it's not selecting the 3rd and 4th rows,

Then I tried the following query

SELECT * FROM MyTable
WHERE RN BETWEEN 2 AND 5

This also syntactically and progrmatically correct, but not selecting the exact columns.

Answer 1

Use a subquery:

SELECT t.*
FROM (SELECT t.*, ROWNUM as rn
      FROM MyTable t
     ) t
WHERE rn > 2 AND rn < 5;

Note that tables represent unordered sets. There is no such thing as a first or second row. You should have an ORDER BY clause to specify the ordering.

The reason that your version doesn't work is that rownum starts at 1 when the first row is put into the result set. If no row is put in, the value never increments. So, it never hits 2 or 3.

I should also note that between in SQL is inclusive. So >= and <= are more appropriate.

EDIT:

I should note that Oracle 12+ supports FETCH/OFFSET:

select t.*
from mytable t
offset 2                  -- start on the third row
fetch first 2 rows only   -- fetch two rows in total

An order by is still recommended in this case.

Answer 2

A little bit of analytics.

Salaries in the EMP table, sorted by $$$, look like this:

SQL> select ename, sal
  2  from emp
  3  order by sal;

ENAME             SAL
---------- ----------
SMITH             800
JAMES             950   2   you want to return James ...
WARD             1250   3
MARTIN           1250   4
MILLER           1300   5   ... to Miller
TURNER           1500
ALLEN            1600
CLARK            2450
BLAKE            2850
JONES            2975
FORD             3000
KING             5000

12 rows selected.

SQL>

If you do it as follows, you'd get what you wanted:

SQL> select ename, sal, rn
  2  from (select ename, sal, row_number() over (order by sal) rn
  3        from emp
  4       )
  5  where rn between 2 and 5;

ENAME             SAL         RN
---------- ---------- ----------
JAMES             950          2
WARD             1250          3
MARTIN           1250          4
MILLER           1300          5

SQL>

However, as you can see, Ward and Martin earn the same $1250. So, should we count them as having the same salary and include Turner into the list, or not? Yet two another analytic functions might help you decide: RANK and DENSE_RANK:

SQL> select ename, sal,
  2    row_number() over (order by sal) rn,
  3    rank() over (order by sal) rnk,
  4    dense_rank() over (order by sal) drnk
  5  from emp
  6  order by sal;

ENAME             SAL         RN        RNK       DRNK
---------- ---------- ---------- ---------- ----------
SMITH             800          1          1          1
JAMES             950          2          2          2   2nd isn't questionable, but ...
WARD             1250          3          3          3
MARTIN           1250          4          3          3
MILLER           1300          5          5          4   ... which one is 5th? Miller (RN and RNK), ...
TURNER           1500          6          6          5   ... or Turner (DRNK column)?
ALLEN            1600          7          7          6
CLARK            2450          8          8          7
BLAKE            2850          9          9          8
JONES            2975         10         10          9
FORD             3000         11         11         10
KING             5000         12         12         11

12 rows selected.

SQL>

To be fair, DENSE_RANK is probably the best option in such cases:

SQL> select ename, sal, drnk
  2  from (select ename, sal, dense_rank() over (order by sal) drnk
  3        from emp
  4       )
  5  where drnk between 2 and 5;

ENAME             SAL       DRNK
---------- ---------- ----------
JAMES             950          2
WARD             1250          3
MARTIN           1250          3
MILLER           1300          4
TURNER           1500          5

SQL>

Now you have several options; pick the one that suits you best.