How to return specific row number of a Hierachial query - Oracle I have this query that works as expected. Within Oracle SQL Developer it returns ~19,500 rows.  Unfortunately our current application limits our result to 10,000.  I know I will need to run this query multiple times and merge the resultsets. But I need to be able to limit query results in blocks of 10,000.

Adding a Where clause before the Start statement only works for the first execution:

Where RowNum Between 1 and 10001 works as expected.

Where RowNum Between 10001 and 20001 returns no rows.

Based on my testing, limiting by Level will not work due to the number of number of children within each level.

Thanks for all your help!

Select WFS.WORKFLOWSTEPID

, WFS.STEPTYPE

, WFS.SUBWORKFLOWBASEID

, WFS.SUBWORKFLOWID

, WFS.Sequence

, WFS.WORKFLOWSTEPNAME

, WFS.Sequence ParentSEQ

, WFB.WorkflowBaseID

, WFB.WORKFLOWNAME

, WFS.WORKFLOWID

, Level

from WorkflowBase WFB

Inner Join Workflow WF on WFB.REVOFRCDID = WF.Workflowid

Inner Join WorkflowStep WFS on WF.WORKFLOWID = WFS.WorkflowID

Start With WFS.WORKFLOWID =  '48810e80000001ca' 

Connect by Prior WFS.SubWorkflowBaseID = WFB.WorkflowBaseID

Order Siblings by ParentSeq, Sequence
See this article from Oracle why the rownum approach won't work:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns009.htm

See this article on StackOverflow for the solution:
https://stackoverflow.com/questions/13351318/table-rownum-returns-0-records-if-rownum-is-tested-for-value-greater-than-100

Edit (for those who don't like reading):
- you want to do something like this

select count(*)
from (select rownum r
        from some_table
        order by r)
where r&gt;=101 
and rownum &lt;= 400;