Nested loop for retrieving specific row doesn't work

Question

The below code doesn't work. The while loop doesn't display any values. If I change it to 0 and 150 it works fine. Anything other than 0 doesn't retrieve any value. I am using an Oracle database. I tried using ORDER BY but it still doesn't work.

ResultSet rset1 = stmt.executeQuery
    (" SELECT * FROM (SELECT * FROM iris ) WHERE rownum BETWEEN 10 and 150");
while(rset1.next())
{
    System.out.println(rset1.getString(1));
}
/////////////////////////////////////////////
java.util.Properties props = new java.util.Properties();
props.setProperty("user", "system");
props.setProperty("password", "weblogic");

DriverManager.registerDriver(new OracleDriver());
Connection conn = DriverManager.getConnection(url, props);

Statement stmt = conn.createStatement();

ResultSet rset1 = stmt.executeQuery(" WITH q AS  (SELECT * FROM iris ) 
                                      SELECT * 
                                        FROM q 
                                       WHERE ROWNUM BETWEEN 10 and 150");

while(rset1.next())
{
   System.out.println(rset1.getString(1));
}

Answer 1

Try:

SELECT x.* 
  FROM (SELECT t.*,
               ROWNUM AS rn
          FROM iris t ) x
 WHERE x.rn >= 10
   AND ROWNUM <= 150

Answer 2

ROWNUM is a bit weird. The first row in a resultset has the ROWNUM of 1. But if you use it in a WHERE clause it filters out rows in a resultset.

Say I start with

select rownum, table_name from all_tables where rownum in (1,2,3);

         ROWNUM TABLE_NAME
--------------- -----------
           1.00 CON$
           2.00 UNDO$
           3.00 CDEF$

Then I change to

select rownum, table_name from all_tables where rownum in (1,3);

         ROWNUM TABLE_NAME
--------------- -----------
           1.00 CON$

I only get CON$. I can't have a third row unless I've got a second row. By saying I never want row 2, I never see row 3 because I exclude every potential row.

 select rownum, table_name from all_tables 
 where rownum in (1,3) or table_name = 'CDEF$';

         ROWNUM TABLE_NAME
--------------- -----------
           1.00 CON$
           2.00 CDEF$
           3.00 CCOL$

Now it gets fun. CON$ qualified as ROWNUM 1, UNDO$ got excluded because it wasn't rownum 1 or 3 or had a name of 'CDEF$'. But CDEF$ qualifies and gets awarded ROWNUM 2 which means a third row can now be included.

OMG Ponies solution should work. There is a similar issue here

Answer 3

I'm going to hijack OMG's answer, in order to clearly explain the problem and the solution.

Your original query is

WITH q AS  (SELECT * FROM iris ) 
SELECT * 
FROM q 
WHERE ROWNUM BETWEEN 10 and 150

Anyway, as OMG says, this is (nice) syntactic sugar for :

SELECT * 
FROM (SELECT * FROM iris )
WHERE ROWNUM BETWEEN 10 and 150

The problem is quite simple. ROWNUM is a virtual column that applies to the result set at the level where the ROWNUM occurs only.

In short, this SQL is the same as :

SELECT * FROM iris WHERE ROWNUM BETWEEN 10 and 150

which returns no rows as it fails at the first test (every possible rownum 1 fails the WHERE clause test, so there is never a ROWNUM 1, let alone a ROWNUM 10). It's not very intuitive, but that is how it works.

The solution to this (provided by OMG) is to convert the ROWNUM into a column in the inner-select BEFORE doing the filter on ROWNUM. I've added an order by to the SQL as this is quite typical.

SELECT x.* 
FROM (SELECT t.*,
           ROWNUM AS rn
      FROM iris t
      ORDER BY something_on_iris ) x
WHERE x.rn >= 10
AND x.rn <= 150

I suspect this is what you were trying to do using the WITH clause? The important point is converting ROWNUM to rn in the 'inner select' - at the level below your filter.

Note : Oracle will recognize this approach - it doesn't do a full scan/sort on iris, but gets the first N matching records (you will see STOPKEY in the explain plan).

Answer 4

Try changing it to

WITH q AS  (SELECT * , rownum myrownumber FROM iris ) 
SELECT * FROM   q
WHERE myrownumber BETWEEN 10 and 150

This might make it work.

Its got to do with how the rownum gets evaluated and i have seen similar kind of issues when migrating my database from Oracle 9i to 10g.

A look at this topic might help!