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)
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
User contributions licensed under CC BY-SA 3.0