I have a resultset with parent and child rows. (Child rows never have childs). I need to do pagination on it (considering the sort) so that:
First need to select only the parents on the pagination page (for example, when page size = 10, it must contain <= 10 parent rows), and then "stick" to them the childs of those parents who are in the pagination page.
The source resultset looks like:
+----+-----------+-------------+
| ID | PARENT_ID | SORT_COLUMN |
+----+-----------+-------------+
| 1 | | y |
| 2 | 1 | z |
| 3 | | u |
| 4 | | q |
| 5 | 4 | o |
| 6 | 4 | p |
| 7 | | c |
+----+-----------+-------------+
The ~desired result:
+----+-----------+-------------+----+----------+
| ID | PARENT_ID | SORT_COLUMN | RN | RN_CHILD |
+----+-----------+-------------+----+----------+
| 7 | | c | 1 | |
| 4 | | q | 2 | |
| 5 | 4 | o | 2 | 1 |
| 6 | 4 | p | 2 | 2 |
| 3 | | u | 3 | |
| 1 | | y | 4 | |
| 2 | 1 | z | 4 | 1 |
+----+-----------+-------------+----+----------+
Now I am doing it this way:
with
cte as
(select 1 as id, null as parent_id, 'y' as sort_column from dual
union all
select 2 as id, 1 as parent_id, 'z' as sort_column from dual
union all
select 3 as id, null as parent_id, 'u' as sort_column from dual
union all
select 4 as id, null as parent_id, 'q' as sort_column from dual
union all
select 5 as id, 4 as parent_id, 'o' as sort_column from dual
union all
select 6 as id, 4 as parent_id, 'p' as sort_column from dual
union all
select 7 as id, null as parent_id, 'c' as sort_column from dual)
select
*
from
(select
t.*,
dense_rank() over (order by
case when t.parent_id is null
then
t.sort_column
else
(select t2.sort_column from cte t2 where t2.id = t.parent_id)
end) as RN,
case
when parent_id is null
then
null
else
row_number() over (partition by t.parent_id order by t.sort_column)
end as RN_CHILD
from cte t)
--where RN between :x and :y
order by RN, RN_CHILD nulls first
But I assume that this can be done without unnecessary extra access to the result set. (select t2.sort_column from cte t2 where t2.id = t.parent_id
).
How to do it?
UPD: parents must be sorted by sort_column, and childs within parents also must be sorted by sort_column.
In my case, instead of extra access to the resultset, the connect_by_root
clause can be used:
(also, I noticed that my original SQL contains a bug - the parents with the same sort_column
value have the same RN
value, this is fixed here)
with
cte(id, parent_id, sort_column) as
(select 1, null, 'y' from dual union all
select 2, 1, 'z' from dual union all
select 3, null, 'u' from dual union all
select 4, null, 'q' from dual union all
select 5, 4, 'o' from dual union all
select 6, 4, 'p' from dual union all
select 7, null, 'c' from dual)
select
*
from
(select
t.*,
case when t.parent_id is null
then
row_number() over (partition by parent_id order by t.sort_column)
else
dense_rank() over (order by connect_by_root sort_column)
end as RN as RN,
case
when parent_id is null
then
null
else
row_number() over (partition by t.parent_id order by t.sort_column)
end as RN_CHILD
from cte t
connect by prior id = parent_id
start with parent_id is null)
--where RN between :x and :y
order by RN, RN_CHILD nulls first