rownum won't return records above 100

Question

I have this query:

SELECT *
FROM (
  SELECT v.*,
         ROWNUM recnum
  FROM (
    SELECT hostaddress,
           modulename,
           severity,
           escalation,
           classification,
           description,
           remarks,
           count(remarks) AS tally,
           max(entrydate) AS lastupdate,
           min(entrydate) AS firstupdate
    FROM vw_ericsson_summary
    GROUP BY hostaddress,
             modulename,
             severity,
             classification,
             escalation,
             description,
             remarks
    ORDER BY lastupdate ASC
  ) v
)
WHERE ROWNUM BETWEEN 1 AND 100

This works okay. but when rownum is greater than 100 such as 101, it won't return records anymore.

SELECT *
FROM (
  SELECT v.*,
         ROWNUM recnum
  FROM (
    SELECT hostaddress,
           modulename,
           severity,
           escalation,
           classification,
           description,
           remarks,
           count(remarks) AS tally,
           max(entrydate) AS lastupdate,
           min(entrydate) AS firstupdate
    FROM vw_ericsson_summary
    GROUP BY hostaddress,
             modulename,
             severity,
             classification,
             escalation,
             description,
             remarks
    ORDER BY lastupdate ASC
  ) v
)
WHERE ROWNUM BETWEEN 101 AND 200;

Although records are actually greater than 100, (it's 128). I'm using Oracle for Toad.

Is there any other way of returning s specified row number from a selection such as above?

Answer 1

Using rownum without an order by will give you random results each time you run your query. To do a proper paging, you need to put this into a derived table together with an order by

select *
from (
  select foo.*
  from foo
  order by some_column -- this is important!!
) t
where rownum between 101 and 200;

For more details please read the explanation on rownum works in the manual http://docs.oracle.com/cd/E11882_01/server.112/e41084/pseudocolumns009.htm#SQLRF00255

For a very good discussion on how to do paging efficiently in Oracle (and other DBMS) I can highly recommend this site: http://use-the-index-luke.com/sql/partial-results/fetch-next-page

Edit after revealing the real statement:

My answer doesn't really change: you are not applying an order by in the inner query.

Your current query looks like this:

SELECT *
FROM (
  SELECT v.*,
         ROWNUM recnum
  FROM (
     ....
  ) v
)
WHERE ROWNUM BETWEEN 101 AND 200;

So there is no order by applied to the statement where the rownum is generated.

You need to apply the order by when selecting from derived table named v not inside it (and you don't really need the rownum as recnum in the inner query either)

SELECT *
FROM (
  SELECT *
  FROM (
     ....
  ) v
  ORDER BY lastupdate ASC
)
WHERE ROWNUM BETWEEN 101 AND 200;