SQL - select parent and child records in an order

Question

I got a table which has both question Id and parent question ids in the same table as :

QUES_ID | Ques_Txt  | Parent_Ques_Id
--------+-----------+---------+---------------
     97  |  X       | NULL
     101 |  Y       | NULL
     115 |  Z       | NULL
     198 |  A       | 97
     302 |  B       | 97
     151 |  C       | 101
     91  |  D       | 115
   1110  |  E       | 115
   1111  |  F       | 115

Now, I want to get a result set as follows in the following order:

QUES_ID | Ques_Txt  | Parent_Ques_Id
--------+-----------+---------+---------------
     97  |  X       | NULL
     198 |  A       | 97
     302 |  B       | 97
     101 |  Y       | NULL
     151 |  C       | 101
     115 |  Z       | NULL         
     91  |  D       | 115
   1110  |  E       | 115
   1111  |  F       | 115

Having parent question listed right above the child question, I have researched for a similar query but all i found is how to display parent-child records using left joins or CTEs, but not in the order that I'm looking for as in the above table. Can any one help, I'm still learning.

Answer 1

You can do this using a trick with coalesce:

select *
from t
order by coalesce(parent_ques_id, ques_id), parent_ques_id, ques_id

Sorting logic would be:

1. Sort all parent-child relations one after another (97, 101, 115) coalesce(parent_ques_id, ques_id) - assign a parent it's own id (instead of null) to be sorted with his children
2. Make parent appear on top of each group parent_ques_id - nulls sort before values in ASC order as default (in SQL Server)
3. Sort children by their id ques_id

Answer 2

Use order by clause :

order by (case when Parent_Ques_Id is null 
               then QUES_ID else Parent_Ques_Id end), Parent_Ques_Id, QUES_ID;