SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Displaying Sorted Hierarchies (SQL Spackle)


Displaying Sorted Hierarchies (SQL Spackle)

Author
Message
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)

Group: General Forum Members
Points: 150393 Visits: 41734
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Michael Meierruth
Michael Meierruth
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1636 Visits: 2515
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


Jason-299789
Jason-299789
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3313 Visits: 3232
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
ekoner
ekoner
SSC-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 Visits: 672
Really enjoyed this article, it explained the use of the CTE very well. Great work as usual Jeff.

Measure twice, cut once
Wesley Brown
Wesley Brown
Ten Centuries
Ten Centuries (1.4K reputation)

Group: Moderators
Points: 1389 Visits: 441
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
james-1071007
james-1071007
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 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 Wink
GSquared
GSquared
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39275 Visits: 9730
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)

Group: General Forum Members
Points: 150393 Visits: 41734
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)

Group: General Forum Members
Points: 150393 Visits: 41734
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)

Group: General Forum Members
Points: 150393 Visits: 41734
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search