select * from tbl
order by
case when parentID = 0 then ID
when parentID > 0 then parentID
end, parentID, name

Regards,
Huiyong


"Greg Collins [InfoPath MVP]" <Greg.Collins_AT_InfoPathDev.com> wrote in
message news:OmpKL6L2EHA.204@TK2MSFTNGP10.phx.gbl...
I have some data that I want to select with an ORDER BY statement and be
able to have it sorted in a hierrarchical order.

There will be essentially 1) and ID, and 2) a ParentID.

I would like to have the data returned sorted such that any row with a
ParentID = to an ID is under that ID

So if I have:

NAME / ID / ParentID
name1 / 1 / 0
name2 / 2 / 0
name3 / 3 / 1
name4 / 4 / 5
name5 / 5 / 1
name6 / 6 / 2

I want it to come back sorted as:

name1
name3
name5
name4
name2
name6

Any ideas?

--
Greg Collins [InfoPath MVP]
Please visit: http://www.InfoPathDev.com

That worked great! Thank you!

--=20
Greg Collins [InfoPath MVP]
Please visit: http://www.InfoPathDev.com



"Itzik Ben-Gan" <itzik@REMOVETHIS.SolidQualityLearning.com> wrote in =
message news:#NnAytN2EHA.4028@TK2MSFTNGP15.phx.gbl...
Greg, see if the following function works for you:

CREATE TABLE Tree
(
NAME VARCHAR(10) NOT NULL,
ID INT NOT NULL PRIMARY KEY,
ParentID INT NOT NULL
)

INSERT INTO Tree VALUES('name1',1,0)
INSERT INTO Tree VALUES('name2',2,0)
INSERT INTO Tree VALUES('name3',3,1)
INSERT INTO Tree VALUES('name4',4,5)
INSERT INTO Tree VALUES('name5',5,1)
INSERT INTO Tree VALUES('name6',6,2)
GO

CREATE FUNCTION ufn_TreeSort() RETURNS @t table
(
id int NOT NULL PRIMARY KEY,
parentID int NOT NULL,
NAME varchar(10) NOT NULL,
lvl int NOT NULL,
path varbinary(8000) NOT NULL
)
AS
BEGIN
DECLARE @lvl AS int; SET @lvl =3D 0

-- insert 0 level nodes
INSERT INTO @t
SELECT id, parentID, NAME, @lvl, CAST(id AS BINARY(4))
FROM Tree
WHERE parentID =3D 0

WHILE @@ROWCOUNT > 0
BEGIN
SET @lvl =3D @lvl + 1

-- insert children of nodes in prev level
INSERT INTO @t
SELECT E.id, E.parentID, E.NAME, @lvl,
T.path + CAST(E.id AS BINARY(4))
FROM Tree AS E JOIN @t AS T
ON E.parentID =3D T.id AND T.lvl =3D @lvl - 1
END

RETURN
END
GO

-- test
SELECT REPLICATE(' ', lvl) + NAME
FROM ufn_TreeSort()
ORDER BY path

-- Output:
name1
name3
name5
name4
name2
name6

--=20
BG, SQL Server MVP
www.SolidQualityLearning.com


"Greg Collins [InfoPath MVP]" <Greg.Collins_AT_InfoPathDev.com> wrote in =

message news:OmpKL6L2EHA.204@TK2MSFTNGP10.phx.gbl...
I have some data that I want to select with an ORDER BY statement and be =

able to have it sorted in a hierrarchical order.

There will be essentially 1) and ID, and 2) a ParentID.

I would like to have the data returned sorted such that any row with a=20
ParentID =3D to an ID is under that ID

So if I have:

NAME / ID / ParentID
name1 / 1 / 0
name2 / 2 / 0
name3 / 3 / 1
name4 / 4 / 5
name5 / 5 / 1
name6 / 6 / 2

I want it to come back sorted as:

name1
name3
name5
name4
name2
name6

Any ideas?

--=20
Greg Collins [InfoPath MVP]
Please visit: http://www.InfoPathDev.com

That worked great!
Thanx ever so much!!

--=20
Greg Collins [InfoPath MVP]
Please visit: http://www.InfoPathDev.com



[quoted text, click to view]
Greg,

Here is something similar that might help:

set nocount on
go

create table Hi (
PageID int,
ParentPageID int,
Descrip varchar(20)
)

insert into Hi values (1,null,'County 1')
insert into Hi values (2,1,'C1 Region 1')
insert into Hi values (3,1,'C1 Region 2')
insert into Hi values (4,2,'C1 R1 Unit 1')
insert into Hi values (5,2,'C1 R1 Unit 2')
insert into Hi values (6,3,'C1 R2 Unit 1')
insert into Hi values (7,null,'County 2')
insert into Hi values (8,7,'C2 Region 1')
insert into Hi values (9,1,'C1 Region 3')

select *, 0 as Level, cast(str(pageid,10) as varchar(8000)) as H into =
#Temp
from Hi
where ParentPageID is null

declare @lev int
set @lev =3D 0

while @@rowcount > 0 begin
set @lev =3D @lev + 1
insert into #Temp
select G.*, @lev, T.H+str(G.pageid,10)
from Hi G join #Temp T
on G.ParentPageID =3D T.PageID
and T.level =3D @lev - 1
end

select space(level*3)+descrip from #temp
order by H
go

drop table #temp
drop table Hi


Steve Kass
Drew University

[quoted text, click to view]
about 9 levels deep and any length... this approach doesn't accomplish =
the goal-- but I appreciate the idea.
[quoted text, click to view]