Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to order rows by hierarchy

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?

like image 449
Gleb Avatar asked Sep 30 '15 05:09

Gleb


1 Answers

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")

like image 197
Amit Avatar answered Sep 28 '22 10:09

Amit