How to get number of rows {FOLLOWUP QUESTION FROM ARCHIVES }

[seenu27]

Team,

I am struggling to get the data from the database page by page. I went thro the archives of this forum and i found this query to be very useful and similar to my requirement.

It works like charm for the first page ( i.e. 0-10) but for the next page it is not working as i expected.

There are 11 rows in the table. When i do a query for 0-10 it is working perfectly like the one below.

Here is the query
--------------------
select messageID, parentID, title, author, updatedTime
from
(select messageID, parentID, title, author, updatedTime
from
(select messageID, parentID, title, author, updatedTime
from ent_bulletin_messages
where sectionID=10
order by messageID, parentID)
where rownum < =11)
where rownum > 0

BUT WHEN I CHANGE THE PARAMETERS TO
rownum <=20
where rownum > 10

IT SAYS NO ROWS SELECTED.

I DONT KNOW HOW TO MAKE THAT HAPPEN.

Can anyone please please help me out.

Thanks a million
Seenu

[chrisrlong]

Your statement is not quite correct.

Search for 'Window ROWNUM' on this forum and follow all the threads on this topic.

Here is one of them:
http://www.dbasupport.com/forums/sho...?threadid=5480

Here is the example from the threads:

SELECT
---OUTER.C1
FROM
---(
------SELECT
---------INNER.C1,
---------ROWNUM
------------AS QUERY_ROWNUM
------FROM
---------(
---------SELECT
------------C1
---------FROM
------------TABLE1
---------ORDER BY
------------C1
---------) INNER
------WHERE
---------ROWNUM < 8
---) OUTER
WHERE
---OUTER.QUERY_ROWNUM >= 4

Notice how the rownum is aliased between the second and third layer.

HTH,

- Chris