Alphabetical order in Hierarchical Queries

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

SQL Server 2000 Forums

Transact-SQL (2000)

Alphabetical order in Hierarchical Queries

Author

Topic

baranozgul
Starting Member

20 Posts

Posted - 2005-01-10 : 02:06:51

Hi all,

I am using the common 'lineage' method in representing hierarchy. Here's my table and sample data:

CREATE TABLE [TreeTable] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50),
[Lineage] [varchar] (50),
[Level] [int] NOT NULL ,
[Parent] [bigint] NOT NULL ,
GO

Sample data:
ID ITEM LINEAGE DEPTH PARENT
==============================================
0 ---- K_Item ---- 0 ---------- 1 ------
1 ---- G_Item ---- 0.1 -------- 2 ------ 0
2 ---- A_Item ---- 1 ---------- 1 ------
3 ---- F_Item ---- 1.1 -------- 3 ------ 2
4 ---- E_Item ---- 1.1.1 ------ 3 ------ 3
5 ---- B_Item ---- 1.2 -------- 2 ------ 2
6 ---- D_Item ---- 0.2 -------- 2 ------ 0

To retrive this tree:

--------K_Item
-----------G_Item
-----------D_Item
--------A_Item
-----------F_Item
-------------E_ýtem
-----------B_Item

I Use

Select Replicate('--', Level) + Item
From TreeTable
Order by Lineage

I could not yet figure out how I can alphabetically order my items and get the tree as follows:

--------A_Item
-----------B_Item
-----------F_Item
--------------E_ýtem
--------K_Item
-----------D_Item
-----------G_Item

Do you have any ideas?

_ Baran Özgül

Kristen
Test

22859 Posts

Posted - 2005-01-10 : 02:26:39

You want them by "tree" to first two levels and then alphabetically? Or have I misunderstood.

Your LINEAGE will have to use fixed width numbers in order to sort by tree

Kristen

baranozgul
Starting Member

20 Posts

Posted - 2005-01-10 : 02:51:28

Well Kristen, the intendation is not visible in my question, you are right to misunderstand, I am sorry:

I want full alphabetical order in each level...

I would like to get such a list at the end

-A
---A
---B
---C
-B
---A
---B
---C
---D
-C
---A
---F
-D
---B
---G
---N

is it possible?

Kristen
Test

22859 Posts

Posted - 2005-01-10 : 11:29:37

I'm a bit confused that

K_Item Lineage = 0
A_Item Lineage = 1

and in your "desired order" example you want A_Item first and K_Item later - do you need to have them in reverse order?

But either way you will need to use fixed with numbers in your lineage:


ID      ITEM        LINEAGE     DEPTH     PARENT
==============================================
0 ----  K_Item ---- 0000-------- 1 ------ 
1 ----  G_Item ---- 00000001---- 2 ------ 0
2 ----  A_Item ---- 0001-------- 1 ------
3 ----  F_Item ---- 00010001---- 3 ------ 2
4 ----  E_Item ---- 000100010001 3 ------ 3
5 ----  B_Item ---- 00010002---- 2 ------ 2
6 ----  D_Item ---- 00000002---- 2 ------ 0

Kristen

baranozgul
Starting Member

20 Posts

Posted - 2005-01-10 : 12:10:50

Hi Kristen, Thanks for your interest.

I came up with my version of the lineage method, when I read about the articles on the net. However As you stated, once I assign lineage values to items, than ordering by name becomes impossible.

Maybe my linage method is inadequete for this.

What advantage does "fixed width lineage" provide me with? I still can not order alphabeticaly by item name (ITEM column).

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-01-10 : 14:04:30

I have a tree structure that is sorted alphabetically and by the lineages...

My example of the tree structure is at http://www.seventhnight.com/treestructs.asp

When I implemented this with the alphabetical ordering, I sorted the entire set of data in the desired fashion and then generated a 'sortId' column for the ordered records.

Then when building the lineages, I used the sortId. In order to build the lineage you will have to carry the parentId's and the childId's along to connect the next relationships.

for example


Relationships (pId, cId)
0,1
1,2
1,3
0,4

Items (id, name, sortId)
1 - Silver - 4
2 - Red - 3
3 - Blue - 1
4 - Green - 2

lineage (using id)
001;		Silver
001;002;		Red
001;003			Blue
004;		Green


lineage (using sortId)
002;		Green
004;		Silver
004;001;		Blue
004;003;		Red

Corey

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-01-10 : 14:17:18

in your initial example, can't you just write:

Order by Lineage, [Name]

?

- Jeff

baranozgul
Starting Member

20 Posts

Posted - 2005-01-11 : 00:55:25

Unfortunately not, ordering by lineage stricty set the order, since LINEAGE is uniqe to each row. No [NAME] ordering is possinle afterwards

quote:
Originally posted by jsmith8858

in your initial example, can't you just write:

Order by Lineage, [Name]

?

- Jeff

baranozgul
Starting Member

20 Posts

Posted - 2005-01-11 : 01:04:12

Hi Corey,

I read your article, nice one.

You will need to generate the sortid values each time you insert another tree node into the table, right?

- Baran

Kristen
Test

22859 Posts

Posted - 2005-01-11 : 01:51:28

"You will need to generate the sortid values each time you insert another tree node into the table, right?"

We do that. We have a PATH column and a SEQUENCE column. The SEQUENCE column is created with increments of, say, 10 and when a new row is inserted we use half-the-difference of the adjacent rows; when we run out of numbers we reseed the SEQUENCE column (using the PATH as the order by).

Our rational for this was that to keep using the large varchar PATH column as an ORDER BY column slowed things down too much, so we use SEQUENCE for the ORDER BY instad.

This is to avoid renumbering the thing on every Insert/Move (in our case it is a big table!)

Kristen

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-01-11 : 19:03:59

In my case, my table is relatively small (30k-50k rows) so it doesn't mind the sortId being reseeded on inserts (which are not too often).

It would greatly depend on how often you were updating the paths and how many 'nodes' are in the heirarchy... but I would be it wouldn't be too big of a deal to reseed every time. Or you could do something like what Kristen mentioned... good luck!

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources