How to arrange Sql rows in a specific format

Question

I have table with up to 50 rows... like given below.

ID menu dispOdr ParntID
---------------------  
1   abc  1     0
2   cde  2     0
3   fgh  1     2
4 ghdfdj 2     2
5  tetss 1     1
6 uni    3     0

but I want to be sorted

Like

ID menu dispOdr ParntID
---------------------  
1   abc  1     0
5  tetss 1     1
2   cde  2     0
3   fgh  1     2
4 ghdfdj 2     2
6 uni    3     0 

If have any query please let me know.. thanks in advance.

I am using sql server 2014

Answer 1

I think that the query below produces the required output:

SELECT t1.ID, t1.menu, t1.dispOdr, t1.ParntID
FROM mytable AS t1
LEFT JOIN mytable AS t2 ON t1.ParntID = t2.ID
ORDER BY CASE 
            WHEN t1.ParntID = 0 THEN t1.dispOdr
            ELSE t2.dispOdr
         END,
         CASE 
            WHEN t1.ParntID = 0 THEN 1
            ELSE 2
         END,
         t1.dispOdr

The first CASE expression groups records according to the dispOdr of their parent. The second CASE places parent on the top of its subgroup. Finally, the last expression used in the ORDER BY clause orders all child records within a subgroup.

Note: The above query works with one level of nesting.