Help sorting a Recursive Query

Guys, I need help sorting a recursive query. This is my table

CTLG Table

txtID(PK)
txtParentID(FK)
numSortID
txtKeywords
txtTitle
memContent

I'm using txtParentID as the FK, which matches with txtID, to create the recursion.

This is my current Query

This is my current Query:

Code Snippet

WITH tree (data, id, level, pathstr, numSortID, memContent)

AS (SELECT txtTitle, txtid, 0,

CAST('' AS NVARCHAR(MAX)), numSortID, memContent

FROM CTLG

WHERE txtParentID IS NULL

UNION ALL

SELECT txtTitle, V.txtid, t.level + 1, t.pathstr + '>' + V.txtTitle, V.numSortID, v.memContent

FROM CTLG V

INNER JOIN tree t

ON t.id = V.txtParentID)

SELECT SPACE(level) + data as data, id, level, pathstr, numSortID, memContent

FROM tree

The output is this:

Data ID Level pathstr NumSortID Undergraduate Catalog 1 0 1 History 12 1 >History 7 Academic Calendar 14 1 >Academic Calendar 8 Preface 2 1 >Preface 2 NonDiscrimination Statement 3 1 >NonDiscrimination Statement 3 Accreditation 4 1 >Acreditation 4 Memberships 5 1 >Memberships 5 Mission Statement 8 1 >Mission Statement 6 Fall Calendar 20 2 >Academic Calendar>Fall Calendar 1 Winter Calendar 21 2 >Academic Calendar>Winter Calendar 2 Summer Calendar 22 2 >Academic Calendar>Summer Calendar

3

I need my out put to look like the following:

Undergraduate Catalog Level (0) NumSortID (1)

Preface (1) (2)
NonDiscrimination Statement (1) (3)
Accreditation (1) (4)
memberships (1) (5)
Mission Statement (1) (6)
History (1) (7)
Academic Calendar (1) (8)
Fall Calendar (2) (1)
Summer Calendar(2) (2)
Winter Calendar (2) (3)

The Order that I would like to have is based on NumSortID and by Levels. Like the above example.

you forget the order by clause,

Code Snippet

WITH tree (data, id, level, pathstr, numSortID, memContent)

AS (SELECT txtTitle, txtid, 0,

CAST('' AS NVARCHAR(MAX)), numSortID, memContent

FROMCTLG

WHEREtxtParentID IS NULL

UNION ALL

SELECT txtTitle, V.txtid, t.level + 1, t.pathstr + '>' + V.txtTitle, V.numSortID, v.memContent

FROMCTLG V

INNER JOIN tree t

ON t.id = V.txtParentID)

SELECT SPACE(level) + data as data, id, level, pathstr, numSortID, memContent

FROMtree

Order By numSortID

|||

Well no actually I didn't, except that it does sort them out by numSortID, except that it doesn't take into account the levels. Some items that are in level 2 also have a numSortID of 1, 2, and 3. So the item that has level 2 with a numSortID of 1 should not be at the top. it should actually be at the bottom since it's level 2.

|||

Here it is,

Create Table #ctlg (

[Data] Varchar(100) ,

[ID] int ,

[Parent] int

);

Insert Into #ctlg Values('UndergraduateCatalog','1','0');

Insert Into #ctlg Values('History','12','1');

Insert Into #ctlg Values('AcademicCalendar','14','1');

Insert Into #ctlg Values('FallCalendar','20','14');

Insert Into #ctlg Values('WinterCalendar','21','14');

Insert Into #ctlg Values('SummerCalendar','22','14');

Insert Into #ctlg Values('Preface','2','1');

Insert Into #ctlg Values('NonDiscriminationStatement','3','1');

Insert Into #ctlg Values('Accreditation','4','1');

Insert Into #ctlg Values('Memberships','5','1');

Insert Into #ctlg Values('MissionStatement','8','1');

WITH tree (data, id, level, pathstr, sortkey)

AS (

SELECT [Data], [ID], 0, CAST([Data] AS NVARCHAR(MAX)), CAST(10000000 + [ID] AS NVARCHAR(MAX))

FROM #CTLG

WHERE [Parent]=0

UNION ALL

SELECT V.[Data], V.[ID], t.level + 1, t.pathstr +'>'+ V.[Data], CAST(t.sortkey + CAST(10000000 + V.[ID] AS NVARCHAR(MAX)) AS NVARCHAR(MAX))

FROM #CTLG V

INNER JOIN tree t

ON t.id = V.[Parent])

SELECT

data, id, level, pathstr

FROM

tree

order By sortkey

|||

It works! But can you explain to me how you came up with this. I would like to understand it as well.

|||

Simple, as per your requirement you have to sort with Path. Instead of the Name you want to use the Id of the each node. You already have the ‘named’ path. But that won’t suite for your requirement. So I created on more path with ID. Since Id is a integer I made the numbers as same length (adding 100000). So when you create a path with id & use it for the sorting your desired result came.

|||

Very, very Cool!