Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have the following structure of table.

  serial_num integer NOT NULL,
  parent_num integer,
  parent_key text,
  key_ary_num integer[],
  level integer,
  rank integer,
  tree_no text,
  subject1 text,
  subject2 text,

I need write a query for hierarchical sorting.

In the following sample, "tree_no" is drawn for expected sorting: The same kind of sorting for all other columns also need to be sorted like parent child separate sorting. I've been trying with recursive. Any other dynamic solution ?

enter image description here

Here I created SQL Fiddle link: http://sqlfiddle.com/#!12/b93f9/1

Thank you !

share|improve this question
What exactly have you tried? Is there any useful code? –  HAL9000 Oct 7 '13 at 5:50
@CORRUPT, Here I created SQL Fiddle link and checking <sqlfiddle.com/#!12/b93f9/1>; –  gnabhan Oct 7 '13 at 6:46
Sorry, what exactly is your question? What output do you want to create? –  a_horse_with_no_name Oct 7 '13 at 7:30
@a_horse_with_no_name: How to sort (Ascending / Descending order) hierarchical tree structure table. –  gnabhan Oct 7 '13 at 9:11
I still don't understand how your desired output should look like. Can you please post an example on what you want the output to look like based on the given sample data. –  a_horse_with_no_name Oct 7 '13 at 10:06
show 2 more comments

1 Answer

Efficiently storing, processing and reporting on hierarchical data of mixed/unknown depth in SQL is quite a complex area: whole books (such as Joe Celko's "Trees and Hierarchies in SQL for Smarties") have been written on the subject.

What you have there is often called a "naive tree" (only the parent relationship is noted) which does not allow for easily performing such a sort, or for queries where you want all data from under a particular node. It is common to de-normalise the structure in these cases by adding a path element to each row, so you can perform the sort you are looking for by sorting by theis field (and you can perform "everything under no matter how deep" queries by using filters such as tree_path LIKE '/path/to/target/node/%' (or tree_path LIKE '/path/to/target/node%' if you want to include the node itself). This avoids any need for recursion or an arbitrarily long collection joins, though of course you have the extra work to do maintaining this path when the tree changes. The book I usually recommend to all DB people (devs and admins alike), "SQL Antipatterns", has a chapter on this which covers the basics of this and other alternatives quite clearly (so you don't need to try consume Calko's tome unless you have some more complex requirements!).

If you can't alter the structure then you might be able to produce the path as an output using a recursive CTE (Common Table Expression) - I'll not go into detail here as there are already many good examples out there (in the documentation and StackExchange questions such as http://stackoverflow.com/questions/3307480/postgresql-recursive-with).

If your tree has a fixed depth (all leaf nodes are the same number of levels below root) then a fixed set of joins will do the trick:

SELECT {stuff}
FROM   source_table t1
JOIN   source_table t2 ON t2.id=t1.parent
JOIN   source_table t3 ON t3.id=t2.parent
JOIN   source_table t4 ON t4.id=t3.parent
ORDER BY t1.name, t2.name, t3.name, t4.name

for three levels below your root(s). If your depth is not fixed then this would not work (well, it could be forced to up to an arbitrary depth but the joins and the ordering clause would be pretty hairy to design and inefficient to run) to you need to use the CTE approach or use a modified data structure.

share|improve this answer
The question already constains an example using a recursvie CTE. –  a_horse_with_no_name Oct 7 '13 at 9:28
Good point. Though he didn't mention it by that name so mentioning the terminology might have been helpful in giving him terms to use for searching out relevant examples (he said, making excuses for himself!). –  David Spillett Oct 7 '13 at 10:04
Thank you @David Spillett and a_horse_with_no_name. I understood and my case, levels are unlimited. So I use CTE approach. –  gnabhan Oct 7 '13 at 10:15
add comment

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.