CODEWITHSUNDEEP
sql-servert-sql
user2463808
user2463808

Reputation: 179

T-SQL ordering data in hierarchy query from parents to children

I have hierarchical data with parents and children together with positions.

Sample:

create table groups 
(
    Child varchar(1), 
    Parent varchar(1), 
    Position int
);

insert into groups (Child, Parent, Position) 
values ('A', NULL, 1),
       ('B', 'A', 1),
       ('C', 'B', 1),
       ('D', 'B', 2),
       ('E', NULL, 2),
       ('F', NULL, 3),
       ('G', 'F', 1),
       ('H', 'G', 1),
       ('I', 'G', 2),
       ('J', 'F', 2),
       ('K', 'J', 1),
       ('L', 'J', 2);

Each group and subgroup has it own position starting with 1, e.g.:

I need to order the query the way that the children of the parents always come between parents ascending, for example (added levels for better imagination):

Level|Child|Parent|Position
-----+-----+------+---------
  3  |  A  | NULL |   1
  2  |  B  |   A  |   1
  1  |  C  |   B  |   1
  1  |  D  |   B  |   2
  3  |  E  | NULL |   2
  3  |  F  | NULL |   3
  2  |  G  |   F  |   1
  1  |  H  |   G  |   1
  1  |  I  |   G  |   2
  2  |  J  |   F  |   2
  1  |  K  |   J  |   1
  1  |  L  |   J  |   2

This is my current code:

;with cte as 
(
    select Child, Parent, Position 
    from groups

    union all

    select t.Child, t.Parent, t.Position 
    from groups t
    join cte on t.Child = cte.Parent
)
select distinct * 
from cte 
order by Position

db<>fiddle example

Thank you

Upvotes: 0

Views: 165

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82474

Your cte is a little messed up. Here's how I would do it:

;with cte as (
    select Child, Parent, Position As ParentPosition, Position, 1 As level 
    from groups
    where parent is null
    union all
    select t.Child, t.Parent, cte.ParentPosition, t.Position, level + 1
    from groups AS t
    join cte on t.Parent = cte.Child
)
select  * 
from cte 
order by ParentPosition, level, Position

Your cte's anchor part was all the records in the table, where in fact it should be just the records that has no parents (hence the where parent is null).
Also, I've added a column for the parent's position to enable sorting all the children of the same parent before the next parent, and I've added a level column to help order the children by distance from the main parent.

Results:

Child   Parent  ParentPosition  Position    level
A               1               1           1
B       A       1               1           2
C       B       1               1           3
D       B       1               2           3
E               2               2           1
F               3               3           1
G       F       3               1           2
J       F       3               2           2
K       J       3               1           3
H       G       3               1           3
I       G       3               2           3
L       J       3               2           3

Db<>Fiddle

Upvotes: 1

Related Questions