Sorting child elements after their parent element

I'm trying to implement a category table. A simplified table description is like this

id -- name -- parent_id

assuming a sample data like

id - name - parent_id
1 test1  null
2 test2  null
3 test3  null
4 test4  1
5 test5  4
6 test6  2
7 test7  1

I'm struggling to come up with an sql query that will return the record set in the following order

id - name - parent_开发者_如何学Goid
1 test1  null
4 test4  1
5 test5  4
7 test7  1
2 test2  null
6 test6  2
3 test3  null

Basically the child elements are returned after their parent element.

----------------------- SOLVED BY USING LINQ/recursion in code -------------------------

Not exactly an sql solution, but ultimately it works.

Based on what you are trying to do with the query, you don't need to sort it that way. You just need to ensure that the parents are created first. So run your query sorted by parent ID, put the result into an array and loop over that array. On each iteration do a check to make sure parent exists, if it has a parent. If parent doesn't exist, just move that item to the end of the array and go to the next for now, you should only end up with a few cases that get moved so it remains decently efficient.

What I have always done in the past is split the database up into the following (I'm not the best at SQL though so there may be some other solutions for you).

categories
 - category_id  |  int(11)  | Primary Key / Auto_Increment
 ..
 ..

sub_categories
 - sub_category_id  |  int(11)  |  Primary Key / Auto_Increment
 - category_id      |  int(11)  |  Foreign Key to categories table
 ..
 ..

Here is what I would do:

SELECT id, name, parent_id, (CASE WHEN COALESCE(parentid,0)=0 THEN id ELSE (parentid + '.' + id)) as orderid
FROM table
ORDER BY (CASE WHEN COALESCE(parentid,0)=0 THEN id ELSE (parentid + '.' + id))

This should create a new column called orderid that has the parentid dot the id (1.4, 4.5, etc.) For the columns where the parent id is null, it would put just the id. This way you would get the order as 1, 1.4, 4, 4.5, etc.

Please check the code since I wrote this on the fly without testing. It should be close.

The query below works by added an extra order_parent column that contains either the parent id or the row's id, depending on whether it is the parent. It then just sorts primarily by the order_parent id to group them together, then by the parent_id to sort the nulls (actual parents) on top.

Two things:

1. This has one more column that you originally wanted, so just ignore it.
2. In case your database returns the nulls of parent_id last, add a DESC.

Good question, by the way!

SELECT   id,
         name,
         parent_id,
         (
             case
             when parent_id is null then id
             else parent_id
             end
         ) AS order_parent
FROM     myTable
ORDER BY order_parent, parent_id