sql server – SQL – ordering a common table expression – Education Career Blog

Question 1

Using this sample table:

drop table Population
CREATE TABLE dbo.Population(
    PersonId int NOT NULL,
    Name varchar(50) NOT NULL,
    MotherId int NULL,
    FatherId int NULL
) ON PRIMARY
insert Population (PersonId, Name, MotherId, FatherId) values (1, 'Baby', 2, 3)
insert Population (PersonId, Name, MotherId, FatherId) values (2, 'Mother', 4, 5)
insert Population (PersonId, Name, MotherId, FatherId) values (3, 'Father', 6, 7)
insert Population (PersonId, Name, MotherId, FatherId) values (4, 'Mothers Mother', 8, 9)
insert Population (PersonId, Name, MotherId, FatherId) values (5, 'Mothers Father', 99, 99)
insert Population (PersonId, Name, MotherId, FatherId) values (6, 'Fathers Mother', 99, 99)
insert Population (PersonId, Name, MotherId, FatherId) values (7, 'Father Father', 99, 99)
insert Population (PersonId, Name, MotherId, FatherId) values (8, 'Mothers GrandMother', 99, 99)
insert Population (PersonId, Name, MotherId, FatherId) values (9, 'Mothers GrandFather', 99, 99)

I can return all the correct people I needed for a family tree using this SQL

;WITH FamilyTree
AS
(
    SELECT *, CAST(NULL AS VARCHAR(50)) AS childName, 0 AS Generation
    FROM Population
    WHERE PersonId = '1'

    UNION ALL

    SELECT Fam.*, FamilyTree.Name AS childName, Generation + 1
    FROM Population AS Fam
    INNER JOIN FamilyTree
    ON Fam.PersonId = FamilyTree.motherId

    UNION ALL

    SELECT Fam.*, FamilyTree.Name AS childName, Generation + 1
    FROM Population AS Fam
    INNER JOIN FamilyTree
    ON Fam.PersonId = FamilyTree.fatherId

)

SELECT childName, space(generation*2)+name, generation FROM FamilyTree

It gives me:

-baby
--mother
--father
---fathers mother
---fathers father
---mothers mother
---mothers father

But how can I (just using sql) put the tree in the correct order – so that I get:

-baby
--mother
---mothers mother
---mothers father
--father
---fathers mother
---fathers father

Question 2

NB: This answer was written after an extremely superficial glance at the Hierarchies chapter in the book “Inside Microsoft SQL Server T-SQL Querying” Hopefully I didn’t miss any vital caveats!

;WITH FamilyTree
AS
(
    SELECT *, CAST(NULL AS VARCHAR(50)) AS childName, 0 AS Generation, '.' + CAST(PersonId AS VARCHAR(max)) + '.' as Path 
    FROM Population
    WHERE PersonId = '1'

    UNION ALL

    SELECT Fam.*, FamilyTree.Name AS childName, Generation + 1, Path + '0.' + CAST(Fam.PersonId AS VARCHAR(max)) + '.' as Path 
    FROM Population AS Fam
    INNER JOIN FamilyTree
    ON Fam.PersonId = FamilyTree.MotherId

    UNION ALL

    SELECT Fam.*, FamilyTree.Name AS childName, Generation + 1, Path + '1.' + CAST(Fam.PersonId AS VARCHAR(max)) + '.' as Path 
    FROM Population AS Fam
    INNER JOIN FamilyTree
    ON Fam.PersonId = FamilyTree.FatherId

)

SELECT childName, space(Generation*2)+Name, Generation, Path
FROM FamilyTree
ORDER BY Path

Question 3

;WITH FamilyTree
AS
(
    SELECT *, CAST(NULL AS VARCHAR(50)) AS childName, 0 AS Generation, CAST(RIGHT('0000000000' + CAST(PersonId as varchar(10)),10) as varchar(max)) as Descendents
    FROM Population
    WHERE PersonId = '1'

    UNION ALL

    SELECT Fam.*, FamilyTree.Name AS childName, Generation + 1,FamilyTree.Descendents + '|' + RIGHT('0000000000' + CAST(Fam.PersonId as varchar(10)),10)
    FROM Population AS Fam
    INNER JOIN FamilyTree
    ON Fam.PersonId = FamilyTree.motherId

    UNION ALL

    SELECT Fam.*, FamilyTree.Name AS childName, Generation + 1,FamilyTree.Descendents + '|' + RIGHT('0000000000' + CAST(Fam.PersonId as varchar(10)),10)
    FROM Population AS Fam
    INNER JOIN FamilyTree
    ON Fam.PersonId = FamilyTree.fatherId

)

SELECT childName, space(generation*2)+name, generation FROM FamilyTree order by Descendents

Basically, you build up a big sort key, ensuring all Ids at the same level differ at the same position within the string.

Leave a Comment Cancel reply