Recursive CTE Order By Question

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 2005 Forums

Transact-SQL (2005)

Recursive CTE Order By Question

Author

Topic

JBelthoff
Posting Yak Master

173 Posts

Posted - 2010-11-27 : 16:11:58

Hi all,

I have a table with an ID and ParentID that I am doing a recursive common table expression. It lists States, and then the States children.

However I want it to order on alphbetical States and then thier children and I am usure how to do that. I would appreciate any help please...

So I have this:


ID                   PID                  DisplayName               Level
-------------------- -------------------- ------------------------- -----------
1                    0                    New Jersey                0
2                    1                    Central Jersey            1
3                    1                    Jersey Shore              1
4                    1                    North Jersey              1
5                    1                    South Jersey              1
6                    0                    New York                  0
7                    6                    Bronx                     1
8                    6                    Brooklyn                  1
9                    6                    Long Island               1
10                   6                    Queens                    1
11                   6                    Manahatten                1
12                   0                    Connecticut               0
13                   12                   Eastern Connecticut       1
14                   12                   Hartford                  1
15                   12                   New Haven                 1
16                   12                   Northwest Connecticut     1

But I want this:


ID                   PID                  DisplayName               Level
-------------------- -------------------- ------------------------- -----------
12                   0                    Connecticut               0
13                   12                   Eastern Connecticut       1
14                   12                   Hartford                  1
15                   12                   New Haven                 1
16                   12                   Northwest Connecticut     1
1                    0                    New Jersey                0
2                    1                    Central Jersey            1
3                    1                    Jersey Shore              1
4                    1                    North Jersey              1
5                    1                    South Jersey              1
6                    0                    New York                  0
7                    6                    Bronx                     1
8                    6                    Brooklyn                  1
9                    6                    Long Island               1
10                   6                    Queens                    1
11                   6                    Manahatten                1

Here is the code I am currently using:


;With cte_Location 
As 
(
    Select e.ID, e.PID, e.DisplayName, 0 As [Level]
    From dbo.Locations As e
    Where e.PID = 0

    Union All

    Select e.ID, e.PID, e.DisplayName, [Level] + 1
    From dbo.Locations As e
    Inner Join cte_Location As d
        On e.PID = d.ID 
)

Select 
	ID, 
	PID, 
	Cast(DisplayName as varchar(25)) DisplayName, 
	[Level]
From cte_Location 
Order By ID, Level

Any help would be appreciated. Thanks,

JBelthoff
› As far as myself... I do this for fun!

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-11-27 : 17:03:59

Messy, but in my defense, I am half drunk.


With cte_Location
AS
(   Select e.ID, e.PID,e.DisplayName,ROW_NUMBER()  OVER (ORDER BY e.DisplayName)*100 as [LEVEL] 
				   From dbo.Locations As e
				   Where e.PID = 0
	UNION ALL
    Select e.ID, e.PID, e.DisplayName, d.Level+ (ROW_NUMBER() OVER (ORDER BY e.DisplayName)) as [LEVEL]
    From dbo.Locations As e
    inner Join	  (Select e.ID, e.PID,e.DisplayName,ROW_NUMBER()  OVER (ORDER BY e.DisplayName)*100 as [LEVEL] 
				   From dbo.Locations As e
				   Where e.PID = 0)  As d
				On e.PID = d.ID 
	 
)

Select * FROM cte_Location
Order by Level

I get this:


ID	PID	DisplayName	LEVEL
12	0	Connecticut                   	100
13	12	Eastern Connecticut           	104
14	12	Hartford                      	105
15	12	New Haven                     	109
16	12	Northwest Connecticut         	111
1	0	New Jersey                    	200
2	1	Central Jersey                	203
3	1	Jersey Shore                  	206
4	1	North Jersey                  	210
5	1	South Jersey                  	213
6	0	New York                      	300
7	6	Bronx                         	301
8	6	Brooklyn                      	302
9	6	Long Island                   	307
11	6	Manahatten                    	308
10	6	Queens                        	312

Poor planning on your part does not constitute an emergency on my part.

JBelthoff
Posting Yak Master

173 Posts

Posted - 2010-11-27 : 17:09:06

Awesome Thank you very much!!!!!

JBelthoff
› As far as myself... I do this for fun!

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-11-27 : 17:10:03

Check my edit...I had to post the right thing...need the *100 , not *10 or it gets whacked.

Poor planning on your part does not constitute an emergency on my part.

JBelthoff
Posting Yak Master

173 Posts

Posted - 2010-11-27 : 17:45:11

Yes I already anticipated that. Depening on how many total children there could be determines the *100 or greater.

Thanks again!

JBelthoff
› As far as myself... I do this for fun!

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-11-27 : 17:54:16

You are welcome.

Edit: Now that I thinking clearly..it would be far more practical two separate this into two tables to begin with...have a State table, and a SubState table which contains the state id...not sure why you chose this table structure...

Poor planning on your part does not constitute an emergency on my part.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-11-28 : 00:12:29

I would also suggest to use 2 seperate tables

if it's only 2 levels, you can use



;With cte_Location 
As 
(
    Select e.ID, e.PID, e.DisplayName, 0 As [Level],e.displayname  as sort
    From dbo.Locations As e
    Where e.PID = 0

    Union All

    Select e.ID, e.PID, e.DisplayName, [Level] + 1,d.displayname  as sort
    From dbo.Locations As e
    Inner Join cte_Location As d
        On e.PID = d.ID 
)

Select 
	ID, 
	PID, 
	Cast(DisplayName as varchar(25)) DisplayName, 
	[Level]
From cte_Location 
Order By sort, Level,id

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-11-28 : 00:21:48

here's a better version of my above query that will work regardless of levels.



;With cte_Location 
As 
(
    Select e.ID, e.PID, e.DisplayName, 0 As [Level],cast(e.displayname as varchar(200))  as sort
    From dbo.Locations As e
    Where e.PID = 0

    Union All

    Select e.ID, e.PID, e.DisplayName, [Level] + 1,cast(sort + d.displayname as varchar(200))  as sort
    From dbo.Locations As e
    Inner Join cte_Location As d
        On e.PID = d.ID 
)

Select 
	ID, 
	PID, 
	Cast(DisplayName as varchar(25)) DisplayName, 
	[Level]
From cte_Location 
Order By sort,id

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-11-28 : 06:22:58

Nice..thanks Vinnie for the improvement. I didn't think you could use the CTE within itself, but that works nicely.

Poor planning on your part does not constitute an emergency on my part.

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-28 : 12:15:43

Gee...I was gonna say to normalize the data...

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-11-28 : 12:56:21

I think I noted that above...but it isn't against policy to offer a solution of some sort..even if the structure is not ideal..is it?

Poor planning on your part does not constitute an emergency on my part.

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-28 : 14:35:14

quote:
Originally posted by dataguru1971

I think I noted that above...but it isn't against policy to offer a solution of some sort..even if the structure is not ideal..is it?

Poor planning on your part does not constitute an emergency on my part.

Nope...it's the majority of questions posted

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2010-11-28 : 16:56:58

quote:
Originally posted by X002548

Gee...I was gonna say to normalize the data...

Brett

Ok... now that you've said that, how would you "normalize" hierarchical data?

--Jeff Moden

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-11-28 : 17:10:38

He was referring to the State should not be kept in the same table with the city. That would greatly simplify the query for this scenerio, or future ones he will run into.




Declare @mystate table(stateid int,mystatedesc varchar(20))
Declare @mycity table(id int,stateid int, mycitydesc varchar(20))
insert into @mystate(stateid,mystatedesc)
select 1,'Connecticut' union all
select 2,'New York'

insert into @mycity(id,stateid ,mycitydesc)
select 1,1,'hartford' Union all
select 2,1,'New haven' Union all
select 3,2,'Bronx' Union all
select 4,2,'Brookland' 

--Then to query the appropriate  data is much simpler
select * from
@mystate a
inner join
@mycity b
on a.stateid = b.stateid
order by mystatedesc

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2010-11-29 : 09:43:50

That certainly works for this problem. Of course, this problem only has two levels in the "hierarchy". If it had, say, 10 levels, it would be a horse of a different color. ;-)

--Jeff Moden

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2010-11-29 : 16:23:07

quote:

Originally posted by Vinnie881

here's a better version of my above query that will work regardless of levels.



;With cte_Location 
As 
(
    Select e.ID, e.PID, e.DisplayName, 0 As [Level],cast(e.displayname as varchar(200))  as sort
    From dbo.Locations As e
    Where e.PID = 0

    Union All

    Select e.ID, e.PID, e.DisplayName, [Level] + 1,cast(sort + d.displayname as varchar(200))  as sort
    From dbo.Locations As e
    Inner Join cte_Location As d
        On e.PID = d.ID 
)

Select 
	ID, 
	PID, 
	Cast(DisplayName as varchar(25)) DisplayName, 
	[Level]
From cte_Location 
Order By sort,id

This solution is safer than the solution that suggested to multiply by 100 or whatever but even safer would be to change the above line to: Select e.ID, e.PID, e.DisplayName, [Level] + 1,cast(sort + '\' + d.displayname as varchar(200)) as sort

since '1' + '12' is not the same as '11' + '2'!

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-11-29 : 22:04:35

A separator would ensure the sort field was always isolated, but it would also be far more likely that an issue would be the varchar(200) being too short than the sort issue you mentioned (using States and Cities as illustrated, there is close to no chance of a incorrect sort that I can think of, but if multiple levels were introduced I can see that field exceeding 200 characters).

I am pointing this out because changing the sort field how you mentioned might make this a "Safer" query, but until you properly modify the ENTIRE query for a live scenario and the data you will use, it is not a "Safe" query, nor is it intended in any way to be interpreted as that. It was written to show a method, and prior to using it in a live scenario it should be modified to fit someone's needs.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2010-11-30 : 09:33:40

sure, varchar(2000), even safer.

Here's a scenario showing the separator could be essential:
ID PID DisplayName Sort without separator
-------------------- -------------------- ------------------------- -----------
11 0 New Jersey 110
2 11 Central Jersey 112
1 0 Connecticut 10
12 1 Eastern Connecticut 112

Oh I'm sure this is a bad example. But even if we can't come up with a good example that will fail, better to be safe in case the user comes up with a good example that will fail.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-11-30 : 13:27:50

Maybe I didn't state that very clearly, I am agreeing with you that it was very good modification. I was just pointing out the query as presented was to illustrate a method, and if the goal was to make it safer, there would be more obvious modifications you would make prior to implementing in a live scenario.

The examples you provided, and even "New York, New York" should still have sorted correctly in theory(I am actually interested for just personal curiosity if there is any combination of city and states at any level that it would not sort correctly).

Even though it's not related to anything, if anyone is bored and can think of a combination, let me know, I'm curious now!

Thanks

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2010-11-30 : 13:44:09

I just realized I misread the code anyways.

I was involved with a project and we used as a sort column: ChildID + '\' + ParentID

It worked well for what we were doing but I guess the situation is this post is different anyways.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources