Oracle 11g hierarchical queries with limit, offset and order by to achieve pagination on the root level I'm using Oracle 11g (unfortunately not 12c, i.e. OFFSET n ROWS FETCH NEXT m ROWS ONLY is not available) and I'm trying to achieve pagination on a hierarchical query.

The aim is to sort the root level entries and retrieve for a given number of root entries all connected children. The pagination (offset and limit) should be performed by the root entries.

Is there any way to achieve this?

I managed to limit the number of root entries but I cannot achieve the sorting on the root level (START WHITH) or specify the offset only for the root level.

SELECT *
FROM my_table
START WITH (
    column_a = 'some value'
    AND column_b = 'some other value' 
)
AND ROWNUM &lt;= 10 -- limit rows on top level, however the sorting is arbitrary
                 -- and offset is not possible
CONNECT BY
    PRIOR childid = parentid
    AND column_b = 'whatever'
ORDER BY sort_column  -- applies to overall result, alternatively you could 
                      -- use ORDER SIBLINGS BY to sort the entries by their level


Of course it is possible to to some pagination on the overall result by using a subselect like this:

SELECT * FROM (
    SELECT *, rownum as row_num
    FROM my_table
    START WITH (
        column_a = 'some value'
        AND column_b = 'some other value' 
    )
    CONNECT BY
        PRIOR childid = parentid
        AND column_b = 'whatever'
    ORDER BY sort_column
)
WHERE row_num BETWEEN &lt;offset&gt; and &lt;offset+limit&gt;


however, this statement would not limit the number of root entries but the overall entries, i.e. it chops off child nodes, as I do not know, how many children exist for a root node.
With the help of Philᵀᴹ's and Joe Obbish's comments I was able to create a query based on recursive CTEs (Common Table Expressions) that allows pagination on the top level of a hierarchical table.

The SQL statement looks like this:

WITH my_hierarchy (&lt;column1&gt;, &lt;column2&gt;, ...) AS (
  SELECT &lt;column1&gt;, &lt;column2&gt;, ... 
  FROM (
    SELECT &lt;column1&gt;, &lt;column2&gt;, ... , row_number() OVER (ORDER BY &lt;order_column&gt; ASC) row_num
    FROM my_table mt
    WHERE mt.&lt;column1&gt; = 'some value'
      AND mt.&lt;column2&gt; = 'other value'
  )
  WHERE row_num between &lt;offset&gt; and &lt;offset+limit&gt; -- pagination on top level
  UNION ALL
  SELECT mt.&lt;column1&gt;, mt.&lt;column2&gt;, ...
    FROM my_table mt
    INNER JOIN my_hierarchy mh   -- recursive self join
        ON mt.parentId = mh.id
)
SELECT *
FROM my_hierarchy