How to filter recursive CTE SQL queries

0

I have a recursive SQL query that works well for what I want it to do, however I'm not sure how I should filter it in order to the branches filtered alphabetically. For example:

UniqueID | Description | ParentID | MaterializedPath
1          CCC           0          0x00000001
2          BBB           0          0x00000002
3          AAA           0          0x00000003
4          CCCC          1          0x0000000100000004
5          BBBB          1          0x0000000100000005
6          AAAA          2          0x0000000200000006
7          DDDD          2          0x0000000200000007
8          EEEEE         4          0x000000010000000400000008
9          AAAAA         4          0x000000010000000400000009
10         CCCCC         6          0x00000002000000060000000A
11         BBBBB         6          0x00000002000000060000000B
12         FFFFFF        8          0x0000000100000004000000080000000C
13         BBBBBB        8          0x0000000100000004000000080000000D
14         GGGGGG        10         0x00000002000000060000000A0000000E
15         DDDDDD        10         0x00000002000000060000000A0000000F

Returns:

UniqueID | Description | ParentID | MaterializedPath
1          CCC           0          0x00000001
4          CCCC          1          0x0000000100000004
8          EEEEE         4          0x000000010000000400000008
12         FFFFFF        8          0x0000000100000004000000080000000C
13         BBBBBB        8          0x0000000100000004000000080000000D
9          AAAAA         4          0x000000010000000400000009
5          BBBB          1          0x0000000100000005
2          BBB           0          0x00000002
6          AAAA          2          0x0000000200000006
10         CCCCC         6          0x00000002000000060000000A
14         GGGGGG        10         0x00000002000000060000000A0000000E
15         DDDDDD        10         0x00000002000000060000000A0000000F
11         BBBBB         6          0x00000002000000060000000B
7          DDDD          2          0x0000000200000007
3          AAA           0          0x00000003

But what I'd actually like it to do is sort each branch by the description so that it returns like this. So that each level is sorted by description within each branch, so those with no parentID are ordered, and then the children of that branch are ordered below it and so on. e.g A |-A |-A |-B |-C |-B |-C B C

So my data above would return like this.

UniqueID | Description | ParentID | MaterializedPath
3          AAA           0          0x00000003
2          BBB           0          0x00000002
6          AAAA          2          0x0000000200000006
11         BBBBB         6          0x00000002000000060000000B
10         CCCCC         6          0x00000002000000060000000A
15         DDDDDD        10         0x00000002000000060000000A0000000F
14         GGGGGG        10         0x00000002000000060000000A0000000E
7          DDDD          2          0x0000000200000007
1          CCC           0          0x00000001
5          BBBB          1          0x0000000100000005
4          CCCC          1          0x0000000100000004
9          AAAAA         4          0x000000010000000400000009
8          EEEEE         4          0x000000010000000400000008
13         BBBBBB        8          0x0000000100000004000000080000000D
12         FFFFFF        8          0x0000000100000004000000080000000C

And this is my SQL Query:

WITH T AS
(
    SELECT
        N.UniqueID, N.DESCRIPTION, N.ParentID, CAST(N.UniqueID AS varbinary(512)) AS MaterializedPath
    FROM
        TEMPTABLE1 N
    WHERE
        N.ParentID = 0

    UNION ALL

    SELECT
        N.UniqueID, N.DESCRIPTION, N.ParentID, CAST( T.MaterializedPath + CAST(N.UniqueID AS binary(4)) AS varbinary(512) ) AS MaterializedPath
    FROM
        TEMPTABLE1 N INNER JOIN T
            ON N.ParentID = T.UniqueID
)
SELECT *
FROM T
ORDER BY T.MaterializedPath

Example table set up:

CREATE TABLE TEMPTABLE1 (
[UniqueID] [int] IDENTITY(1,1) NOT NULL,
Description NVARCHAR(10) NULL,
ParentID Int NULL
)
GO
INSERT INTO TEMPTABLE1 VALUES ('CCC', 0)
INSERT INTO TEMPTABLE1 VALUES ('BBB', 0)
INSERT INTO TEMPTABLE1 VALUES ('AAA', 0)
INSERT INTO TEMPTABLE1 VALUES ('CCCC', 1)
INSERT INTO TEMPTABLE1 VALUES ('BBBB', 1)
INSERT INTO TEMPTABLE1 VALUES ('AAAA', 2)
INSERT INTO TEMPTABLE1 VALUES ('DDDD', 2)
INSERT INTO TEMPTABLE1 VALUES ('EEEEE', 4)
INSERT INTO TEMPTABLE1 VALUES ('AAAAA', 4)
INSERT INTO TEMPTABLE1 VALUES ('CCCCC', 6)
INSERT INTO TEMPTABLE1 VALUES ('BBBBB', 6)
INSERT INTO TEMPTABLE1 VALUES ('FFFFFF', 8)
INSERT INTO TEMPTABLE1 VALUES ('BBBBBB', 8)
INSERT INTO TEMPTABLE1 VALUES ('GGGGGG', 10)
INSERT INTO TEMPTABLE1 VALUES ('DDDDDD', 10)
sql-server
tsql
asked on Stack Overflow Feb 28, 2020 by RyanBreakspear • edited Mar 2, 2020 by RyanBreakspear

1 Answer

3

You can use multiple columns to sort. In your case it would be :

with T AS(...)
SELECT *
FROM T
ORDER BY T.ParentID, T.DESCRIPTION
answered on Stack Overflow Feb 28, 2020 by Eugene

User contributions licensed under CC BY-SA 3.0