How to order a sql result in this specific way?

Question

I have a table with columns(ID,ParentId,name etc). the parentId can be null. I want to display results in such a way that first it displays parent and in next line it should display its child. How can i order in that way? I am using Microsoft Sql Server

Sample data:

ID          cltName                                            parentId   
----------- -------------------------------------------------- -----------
1           a                                                  NULL       
2           b                                                  NULL       
3           c                                                  NULL       
4           d                                                  NULL       
5           e                                                  NULL       
6           f                                                  NULL       
7           g                                                  6          
8           h                                                  NULL       
9           i                                                  3          
10          k                                                  NULL       
11          yyk                                                NULL       
12          krr                                                NULL       
13          krre                                               12

The expected results are:

    ID          cltName                                            parentId   
    ----------- -------------------------------------------------- -----------
    6           f                                                  NULL       
    7           g                                                  6       
    3           c                                                  NULL       
    9           i                                                  3 
    12          krr                                                NULL       
    13          krre                                               12

Specify the expected result as well. (To make everything very clear.) — jarlh, Jun 19 '18 at 07:13
What about which are not having any parent Ids?? and what RDBMS you are using? — Shakeer Mirza, Jun 19 '18 at 07:30
Which [DBMS product](https://en.wikipedia.org/wiki/DBMS) are you using? "SQL" is just a query language, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using `postgresql`, `oracle`, `sql-server`, `db2`, ... — , Jun 19 '18 at 07:44
you can use Group By with an Order By query. there are explanations here. https://stackoverflow.com/questions/27983/sql-group-by-with-an-order-by — Chill Music Hits, Jun 19 '18 at 07:15
See [this post](https://stackoverflow.com/a/10078414/9338645) for recursive queries on SQL server — Chrᴉz remembers Monica, Jun 19 '18 at 13:04
Why is your expected result in ID order 6, 7, 3, 9 instead of say 3, 9, 6, 7? What it is the rule for this ordering? — DancingFool, Jun 20 '18 at 09:13
there is no specific rule in ordering, it can be 3,9,6,7 or 6,7,3,9 — v379, Jun 20 '18 at 13:41

score 0 · Accepted Answer · answered Jun 21 '18 at 00:10

You need to create a column that has the same value for all the rows you want to group together - this is the parentid or the units own id if it has no parent. Then count how many rows are in each group, and only show rows where there the count is greater than 1. This gets you the right data. Then order by the groupingID, parentID to actually do the sort.

With TestData as 
(
    select 1 as ID,'a' as cltName,NULL as parentId
    union all select 2 ,'b',NULL
    union all select 3 ,'c',NULL
    union all select 4 ,'d',NULL
    union all select 5 ,'e',NULL
    union all select 6 ,'f',NULL
    union all select 7 ,'g',6
    union all select 8 ,'h',NULL
    union all select 9 ,'i',3
    union all select 10,'k',NULL
    union all select 11,'yyk',NULL
    union all select 12,'krr',NULL
    union all select 13,'krre',12
), WorkingData as (
    Select ID, cltName, parentId
    , case when parentId IS NULL then ID else parentId end as groupingID
    , COUNT (ID) OVER (PARTITION BY case when parentId IS NULL then ID else parentId end) as RowCt
    from TestData
)

Select ID, cltName, parentId
From WorkingData 
Where RowCt > 1
Order by groupingID, parentId

How to order a sql result in this specific way?

1 Answers1