Sorting Hierarchy Rows (self-join?)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sorting Hierarchy Rows (self-join?)

Author

Topic

dwooderson
Starting Member

7 Posts

Posted - 2012-09-28 : 15:55:23

I'm extracting data from some application tables and need to sort the rows in my SQL Server 10 staging database based on "PrevSiblingID" and "NextSiblingID" which basically define the the order of the list.

I'll be honest, I'm in over my head here. Any suggestions?

Sample Table and Inserts:

CREATE TABLE [dbo].[A_Hierarchy](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ItemName] [varchar](50) NULL,
[PrevSiblingID] [int] NULL,
[NextSiblingID] [int] NULL)

INSERT INTO A_Hierarchy2 values ('Member G',10,8)
INSERT INTO A_Hierarchy2 values ('Member D',5,7)
INSERT INTO A_Hierarchy2 values ('Member J',4,NULL)
INSERT INTO A_Hierarchy2 values ('Member I',8,3)
INSERT INTO A_Hierarchy2 values ('Member C',9,2)
INSERT INTO A_Hierarchy2 values ('Member A',NULL,9)
INSERT INTO A_Hierarchy2 values ('Member E',2,10)
INSERT INTO A_Hierarchy2 values ('Member H',1,4)
INSERT INTO A_Hierarchy2 values ('Member B',6,5)
INSERT INTO A_Hierarchy2 values ('Member F',7,1)

The desired result is a list of ItemNames, ordered based on the relationship to the other rows as defined in the next/previous columnids.

Desired Result:
Member A
Member B
Member C
...

(the ItemNames won't normally be sortable by alpha like they are in this example)

Any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-28 : 15:56:58

how didnt MemberB come fater A? shouldnt it be MemberC? MemberB's prev sibling is not 9

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dwooderson
Starting Member

7 Posts

Posted - 2012-09-28 : 16:01:18

I think I got that example right.. for "Member B",

PrevSiblingId: 6 (which is Member A)
NextSiblingId: 5 (which is Member C)

and so on.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-28 : 16:01:29

[code]
;With CTE
AS
(SELECT ItemName,NextSiblingID,CAST(1 AS int)AS [Level]
FROM A_Hierarchy2
WHERE PrevSiblingID IS NULL
UNION ALL
SELECT h.ItemName,h.NextSiblingID,c.Level+1
FROM CTe c
INNER JOIN A_Hierarchy2 h
ON h.PrevSiblingID = c.NextSiblingID
)

SELECT itemName
FROM CTE
ORDER BY [Level]
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-28 : 16:02:04

quote:
Originally posted by dwooderson

I think I got that example right.. for "Member B",

PrevSiblingId: 6 (which is Member A)
NextSiblingId: 5 (which is Member C)

and so on.

A is 9 and not 6

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dwooderson
Starting Member

7 Posts

Posted - 2012-09-28 : 16:03:20

Maybe the confusion is that in this example, I'm relying on an identity column to assign the IDs in the order the inserts execute. Sorry, maybe I should have explicitly set that for example purposes. .

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-28 : 16:06:39

Anyways try my last query and see if it fits your need

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dwooderson
Starting Member

7 Posts

Posted - 2012-09-28 : 16:13:25

Result from your query (missing half of the rows)

Member A
Member C
Member E
Member G
Member I

The Next/Prev columns relate the first column, "[ID]". I've tried adapting your query, but have't gotten it yet..

BTW, thanks for your very fast replies!

dwooderson
Starting Member

7 Posts

Posted - 2012-09-28 : 16:17:38

I think I got it:

;With CTE
AS
(SELECT id,ItemName,NextSiblingID,CAST(1 AS int)AS [Level]
FROM A_Hierarchy2
WHERE PrevSiblingID IS NULL
UNION ALL
SELECT h.id,h.ItemName,h.NextSiblingID,c.Level+1
FROM CTe c
INNER JOIN A_Hierarchy2 h
ON h.PrevSiblingID = c.id
)

SELECT itemName
FROM CTE
ORDER BY [Level]

Thank you so much for your help, this is awesome!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-28 : 16:18:59

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources