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.
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;
As noticed by synergetic, the solution for depth-first order provided by Diogo Biazus won't work for id's with different number of digits.
But you can use this solution instead, that uses arrays of integer :
WITH RECURSIVE tree
AS
(
SELECT
node_name, id, parent_id, NULL::varchar AS parent_name, array[id] 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 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;
You can also use the excellent LTree module, but you need to reorganise your data a bit.
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://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/
postgres, im not as good with it.SELECT * FROM table ORDER BY id || id ASC, parent_id ASC ?