SQL - ordering results by parent child

i have entries in my table of products and categories wit开发者_开发百科h columns id and parent.

lets say i have the following

0 ----- 0 ------ home
1 ----- 4 ------ PD1
2 ----- 0 ------ CAT1
3 ----- 2 ------ PD2
4 ----- 2 ------ CAT2

the fist col being the id, second being parent and a title at the end.

is there a way (using ORDER or some other method) of returning the results in the following order?

0 ----- 0 ------ home
    2 ----- 0 ------ CAT1
        3 ----- 2 ------ PD2
        4 ----- 2 ------ CAT2
            1 ----- 4 ------ PD1

---

Try this:

SELECT id, parent, title
FROM yourtable
ORDER BY parent, id

---

try this

 SELECT * FROM yourtablename ORDER BY parentfieldname 

---

could it be as simple as

ORDER BY ParentID, ID

---

Firstly, if you want to order in a custom way (not using PKs or alphabetic on a name field), you need to add a field to define the ordering weight of the various objects. I would add a field to the table called something like ordering_weight - you do not want to use the field name order or sequence b/c they are reserved SQL words.

Secondly, you need an order by clause:

ORDER BY top_level.ordering_weight, next_level.ordering_weight, ..., deepest_level.ordering_weight 

Notice that my order by clause orders first by the highest level of my tree, and last by the lowest or deepest level of the tree.

Of course, disregard the above if all you are looking fo a dynamic level of recursion.

Generally when I see a parent child relationship like this I see people wanting to do more than 1 level of recursion. The problem with your schema is that It doesn't support dynamic levels of recursion as it is. You can only fetch the top level parent's children, every additional level requires another join (there are some clever ways to get past this, but they still require additional SQL per level).

I think what might be more useful to you is to look into the Nested Set Model, which allows querying of infinite levels of recursion. see: http://en.wikipedia.org/wiki/Nested_set_model

For example the following tree of parent child relationships is extremely difficult using standard joins, but is very easy using a model like nested set.

Category A
- Category B
- - Category D
- Category E
Category F
- Category G
- - Category H
- - - Category I
- - - - Category J