Hierarchy ORBER BY - SQL Server Forums

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)

Hierarchy ORBER BY

Author

Topic

si7ikon
Starting Member

2 Posts

Posted - 2004-03-09 : 11:03:40

Hi, I'm trying to figure out how to write I SP on the following table that will produce the results show at the bottom. My main problem is the sorting, is there a way I loop through the records in TSQL and ORDER BY SortSort where the ParentID's are the same?

Thank you.

Table example


ID	ParentID	SortOrder	Caption
1	null		1		Main
2	1		1		Files
3	1		3		Folders
4	2		2		File2
5	1		2		Holders
6	2		1		File1
7	5		2		Holder2
8	5		1		Holder1

Required result example

Main
-Files
--File1
--File2
-Holders
--Holder1
--Holder2
-Folders

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-09 : 11:06:44

Have a look at Expanding Hierarchies in BOL.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-03-09 : 11:09:12

ORDER BY (ISNULL(ParentID, 0) * 1000) + ISNULL(SortOrder, 0)

Duane.

si7ikon
Starting Member

2 Posts

Posted - 2004-03-09 : 11:45:44

Hi, thanks for the responses.

ditch - Dankie, it seems like it works!
ehorn - I looked at the BOL and found what you whre referring too, but a little over kill for what I what.

I also got his very helpful response from the [url]http://www.asp.net[/url] forum - [url]http://www.aspalliance.com/388[/url] if anyone is interested.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-03-09 : 22:10:30

quote:
Originally posted by si7ikon
ditch - Dankie, it seems like it works!

Dis n' Plesier. (Not sure about Afrikaans Spelling).

Duane.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources