Select rows from table using tree order

Question

I have table witch contains fields: id, parent_id, name (etc.)

i want to order this table in "tree travel order" ie.

id  parent_id
1,  0
3,  1
5,  1

2,  0
8,  2

4,  0
9,  4

(...)

in short describe: take root node, append all children, take next root node append children etc.

Answer 1

SELECT * FROM table ORDER BY id,parent_id

That should order my columns in the order there placed within the query.

Unless you mean GROUP the items, witch I think you do, then use

SELECT * FROM table ORDER BY id GROUP BY parent_id

And i also advise you to read this article: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

Answer 2

By your description I assume you mean breadth-first order, which could be easly done using a WITH RECURSIVE query (PostgreSQL 8.4+):

WITH RECURSIVE tree 
AS 
(
    SELECT 
        node_name, id, parent_id, NULL::varchar AS parent_name 
    FROM foo 
    WHERE parent_id IS NULL 
    UNION
    SELECT 
        node_name, f1.id, f1.parent_id, tree.node_name AS parent_name 
    FROM 
        tree 
        JOIN foo f1 ON f1.parent_id = tree.id
) 
SELECT node_name, empno, parent_id, node_name FROM tree;

You could also use depth-first order using the following SQL:

WITH RECURSIVE tree 
AS 
(
    SELECT 
        node_name, id, parent_id, NULL::varchar AS parent_name, id::text AS path 
    FROM foo WHERE parent_id IS NULL 
    UNION
    SELECT 
        node_name, f1.id, f1.parent_id, tree.node_name AS parent_name, tree.path || '-' || f1.id::text AS path 
    FROM 
        tree 
        JOIN foo f1 ON f1.parent_id = tree.id
) 
SELECT node_name, empno, parent_id, node_name, path FROM tree ORDER BY path;

Answer 3

This article discussed hierarchical data structures in relational databases. It talks specifically about MySQL, but is rather generic in fact. It was very helpful for me.

Answer 4

You can also use the excellent LTree module, but you need to reorganise your data a bit.