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 <= 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 <offset> and <offset+limit>
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.
ORDER BY
? You'll be able to use a CTE for this