I have table with hierarchical, parent-child relations and want to order it by that hierarchy. Table is:
id|parent|type
--------------
1 |0 |1
2 |0 |1
3 |0 |1
4 |0 |2
5 |0 |2
6 |2 |2
7 |3 |2
And as result I want this:
id|parent|type
--------------
1 |0 |1
2 |0 |1
6 |2 |2
3 |0 |1
7 |3 |2
4 |0 |2
5 |0 |2
So I want get something like a tree view where type 1 ordered first and type 2 at the end.
Now I'm trying to use recursion but the order is wrong:
with cte as
(
select id, parent, type from tbl where id=1
union all
select id, parent, type,
ROW_NUMBER()over(
order by
(case when t.type = 1 then 1
when t.type = 2 then 2
else 1000
end) as rn
from tbl t
inner join cte c on c.id=t.parent
)
select * from cte
order by rn
How can I do this?
Can be done with the following recursive CTE:
WITH cte AS (
SELECT *,
CAST(ROW_NUMBER() OVER(ORDER BY id) AS REAL) rn,
1 level
FROM tbl
WHERE parent = 0
UNION ALL
SELECT t2.*,
cte.rn + (CAST(ROW_NUMBER() OVER(ORDER BY t2.id) AS REAL) / POWER(10, cte.level)) rn,
cte.level + 1 level
FROM tbl t2 INNER JOIN cte
ON t2.parent = cte.id
)
SELECT id, parent, type
FROM cte
ORDER BY rn
See SQLFiddle with more complicated sample data (deeper hierarchies, "unordered parent-child id's")
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With