Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Displaying Sorted Hierarchies (SQL Spackle) Expand / Collapse
Author
Message
Posted Wednesday, March 9, 2011 9:44 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:14 PM
Points: 37,107, Visits: 31,665
Comments posted to this topic are about the item Displaying Sorted Hierarchies (SQL Spackle)

For some very high performance methods for building/maintaining (54 seconds for a million node hierarchy) from an Adjacency List and using Nested Sets in conjunction with both an Adjacency List and a Hierarchical Path all in one table, please see the following article.
Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets

For information on how to build a pre-aggregated hierarchical table that answers for most of the things you'd ever query a hierarchical table for (MLM'ers will love this one), please see the following article.
Hierarchies on Steroids #2: A Replacement for Nested Sets Calculations


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1075989
Posted Thursday, March 10, 2011 1:38 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 11:59 PM
Points: 542, Visits: 2,122
Jeff,
Neat stuff on hierarchies!
One thing that's confusing me is the use of the back slash and how it orders.
Thus in the script below, why do I get two different ordering results when I order on c1 as opposed to c2?
select *
from
(
select 'AA' c1,CAST('AA' as binary(4)) c2
union all
select 'A\',CAST('A\' as binary(4))
) t
order by c1

Post #1076045
Posted Thursday, March 10, 2011 1:50 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 7:09 AM
Points: 878, Visits: 2,395
Nice Article Jeff, and having spent a lot of time recently working with them its good to see alternative solutions.

One common problem is people come across is when they have to flatten the hierarchy for data warehouses, eg Each level of the tree in a seperate column, and each row containing the entire tree for that Leaf node.


_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1076047
Posted Thursday, March 10, 2011 2:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:27 AM
Points: 37, Visits: 658
Really enjoyed this article, it explained the use of the CTE very well. Great work as usual Jeff.

Measure twice, cut once





Post #1076070
Posted Thursday, March 10, 2011 3:39 AM


SSChasing Mays

SSChasing Mays

Group: Moderators
Last Login: Wednesday, July 23, 2014 1:21 PM
Points: 609, Visits: 407
I prefer to use a two column approach to build a tree instead of the single column style that you have to split, it doesn't scale well. For smaller stuff like this HR representation it should be OK. Trees and Hierarchies in SQL from Joe Celko is one of my all time favorite books! Good article!
your brother,
Phil McCrevice


http://www.sqlserverio.com
http://www.cactuss.org
http://www.salssa.org
Post #1076093
Posted Thursday, March 10, 2011 3:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 3, 2011 4:47 AM
Points: 2, Visits: 53

That doesn't order correctly. You are relaying on the natural data order to provide the correct results.

Try inserting some extra records with lower manager id's and names beginning with z ;)

Post #1076106
Posted Thursday, March 10, 2011 6:41 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
I've used a variation on this solution for sorting before, and found that it works best if you pad numerical values with leading zeroes to a fixed length, before sorting them as character data.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1076196
Posted Thursday, March 10, 2011 6:57 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:14 PM
Points: 37,107, Visits: 31,665
Michael Meierruth (3/10/2011)
Jeff,
Neat stuff on hierarchies!
One thing that's confusing me is the use of the back slash and how it orders.
Thus in the script below, why do I get two different ordering results when I order on c1 as opposed to c2?
select *
from
(
select 'AA' c1,CAST('AA' as binary(4)) c2
union all
select 'A\',CAST('A\' as binary(4))
) t
order by c1



It's because of the collation settings for the server. For c1, it's purely an "alpha" sort based on collation. For c2, collation does not come into effect because it's binary. Since the backslash (ASCII character #92) has a larger ASCII value than the letter "A" (ASCII character #65), it sorts differently for the binary version. It would also sort c1 differently if you used one of the binary collations.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1076212
Posted Thursday, March 10, 2011 7:07 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:14 PM
Points: 37,107, Visits: 31,665
Jason-299789 (3/10/2011)
Nice Article Jeff, and having spent a lot of time recently working with them its good to see alternative solutions.

One common problem is people come across is when they have to flatten the hierarchy for data warehouses, eg Each level of the tree in a seperate column, and each row containing the entire tree for that Leaf node.


In the example I gave, the hierarchical path column does contain the entire tree of ID's or names for that given Leaf node. So far as listing each level of the tree in a separate column goes, that would make for a whole lot of NULLs in many of the columns. I don't believe I'd use such a method because you have to change column names for similar queries on the structure. Instead, I'd recommend using a "Nested Set" hierarchy or the new preaggregated "warehouse" table method that I'll reveal when the larger article I'm writing on hierarchies comes out.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1076222
Posted Thursday, March 10, 2011 7:09 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:14 PM
Points: 37,107, Visits: 31,665
ekoner (3/10/2011)
Really enjoyed this article, it explained the use of the CTE very well. Great work as usual Jeff.


Thanks for the nice compliment, ekoner. I appreciate it.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1076223
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse