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 =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.comORDER BY ID, ParentID, Name
http://www.aspfaq.com/
(Reverse address to reply.)
"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|||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|||Greg Collins [InfoPath MVP] wrote:
> I have some data that I want to select with an ORDER BY statement and be a
ble 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 Par
entID = 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?
>
Doing what you want to do will require some front-end manipulation.
Zach|||This still just mainly sorts by ParentID. As the organization can be =
about 9 levels deep and any length... this approach doesn't accomplish =
the goal-- but I appreciate the idea.
--=20
Greg Collins [InfoPath MVP]
Please visit: http://www.InfoPathDev.com
"Huiyong Lau" <huiyong_lau@.hotmail.com> wrote in message =
news:u35#rAM2EHA.1264@.TK2MSFTNGP12.phx.gbl...
select * from tbl
order by
case when parentID =3D 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 =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|||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 = 0
-- insert 0 level nodes
INSERT INTO @.t
SELECT id, parentID, NAME, @.lvl, CAST(id AS BINARY(4))
FROM Tree
WHERE parentID = 0
WHILE @.@.ROWCOUNT > 0
BEGIN
SET @.lvl = @.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 = T.id AND T.lvl = @.lvl - 1
END
RETURN
END
GO
-- test
SELECT REPLICATE(' ', lvl) + NAME
FROM ufn_TreeSort()
ORDER BY path
-- Output:
name1
name3
name5
name4
name2
name6
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
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|||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 = 0
while @.@.rowcount > 0 begin
set @.lev = @.lev + 1
insert into #Temp
select G.*, @.lev, T.H+str(G.pageid,10)
from Hi G join #Temp T
on G.ParentPageID = T.PageID
and T.level = @.lev - 1
end
select space(level*3)+descrip from #temp
order by H
go
drop table #temp
drop table Hi
Steve Kass
Drew University
Greg Collins [InfoPath MVP] wrote:
>This still just mainly sorts by ParentID. As the organization can be about
9 levels deep and any length... this approach doesn't accomplish the goal--
but I appreciate the idea.
>
>|||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
"Steve Kass" <skass@.drew.edu> wrote in message =
news:u4$P2fP2EHA.3064@.TK2MSFTNGP10.phx.gbl...
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
Greg Collins [InfoPath MVP] wrote:
>This still just mainly sorts by ParentID. As the organization can be =
about 9 levels deep and any length... this approach doesn't accomplish =
the goal-- but I appreciate the idea.
> =20
>sql
Showing posts with label essentially. Show all posts
Showing posts with label essentially. Show all posts
Wednesday, March 28, 2012
ORDER BY question.
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 =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
ORDER BY ID, ParentID, Name
http://www.aspfaq.com/
(Reverse address to reply.)
"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
|||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
|||Greg Collins [InfoPath MVP] wrote:
> 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?
>
Doing what you want to do will require some front-end manipulation.
Zach
|||This still just mainly sorts by ParentID. As the organization can be =
about 9 levels deep and any length... this approach doesn't accomplish =
the goal-- but I appreciate the idea.
--=20
Greg Collins [InfoPath MVP]
Please visit: http://www.InfoPathDev.com
"Huiyong Lau" <huiyong_lau@.hotmail.com> wrote in message =
news:u35#rAM2EHA.1264@.TK2MSFTNGP12.phx.gbl...
select * from tbl
order by
case when parentID =3D 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 =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
|||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 = 0
-- insert 0 level nodes
INSERT INTO @.t
SELECT id, parentID, NAME, @.lvl, CAST(id AS BINARY(4))
FROM Tree
WHERE parentID = 0
WHILE @.@.ROWCOUNT > 0
BEGIN
SET @.lvl = @.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 = T.id AND T.lvl = @.lvl - 1
END
RETURN
END
GO
-- test
SELECT REPLICATE(' ', lvl) + NAME
FROM ufn_TreeSort()
ORDER BY path
-- Output:
name1
name3
name5
name4
name2
name6
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
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
|||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 = 0
while @.@.rowcount > 0 begin
set @.lev = @.lev + 1
insert into #Temp
select G.*, @.lev, T.H+str(G.pageid,10)
from Hi G join #Temp T
on G.ParentPageID = T.PageID
and T.level = @.lev - 1
end
select space(level*3)+descrip from #temp
order by H
go
drop table #temp
drop table Hi
Steve Kass
Drew University
Greg Collins [InfoPath MVP] wrote:
>This still just mainly sorts by ParentID. As the organization can be about 9 levels deep and any length... this approach doesn't accomplish the goal-- but I appreciate the idea.
>
>
|||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
"Steve Kass" <skass@.drew.edu> wrote in message =
news:u4$P2fP2EHA.3064@.TK2MSFTNGP10.phx.gbl...
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
Greg Collins [InfoPath MVP] wrote:
>This still just mainly sorts by ParentID. As the organization can be =
about 9 levels deep and any length... this approach doesn't accomplish =
the goal-- but I appreciate the idea.
> =20
>
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 =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
ORDER BY ID, ParentID, Name
http://www.aspfaq.com/
(Reverse address to reply.)
"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
|||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
|||Greg Collins [InfoPath MVP] wrote:
> 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?
>
Doing what you want to do will require some front-end manipulation.
Zach
|||This still just mainly sorts by ParentID. As the organization can be =
about 9 levels deep and any length... this approach doesn't accomplish =
the goal-- but I appreciate the idea.
--=20
Greg Collins [InfoPath MVP]
Please visit: http://www.InfoPathDev.com
"Huiyong Lau" <huiyong_lau@.hotmail.com> wrote in message =
news:u35#rAM2EHA.1264@.TK2MSFTNGP12.phx.gbl...
select * from tbl
order by
case when parentID =3D 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 =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
|||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 = 0
-- insert 0 level nodes
INSERT INTO @.t
SELECT id, parentID, NAME, @.lvl, CAST(id AS BINARY(4))
FROM Tree
WHERE parentID = 0
WHILE @.@.ROWCOUNT > 0
BEGIN
SET @.lvl = @.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 = T.id AND T.lvl = @.lvl - 1
END
RETURN
END
GO
-- test
SELECT REPLICATE(' ', lvl) + NAME
FROM ufn_TreeSort()
ORDER BY path
-- Output:
name1
name3
name5
name4
name2
name6
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
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
|||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 = 0
while @.@.rowcount > 0 begin
set @.lev = @.lev + 1
insert into #Temp
select G.*, @.lev, T.H+str(G.pageid,10)
from Hi G join #Temp T
on G.ParentPageID = T.PageID
and T.level = @.lev - 1
end
select space(level*3)+descrip from #temp
order by H
go
drop table #temp
drop table Hi
Steve Kass
Drew University
Greg Collins [InfoPath MVP] wrote:
>This still just mainly sorts by ParentID. As the organization can be about 9 levels deep and any length... this approach doesn't accomplish the goal-- but I appreciate the idea.
>
>
|||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
"Steve Kass" <skass@.drew.edu> wrote in message =
news:u4$P2fP2EHA.3064@.TK2MSFTNGP10.phx.gbl...
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
Greg Collins [InfoPath MVP] wrote:
>This still just mainly sorts by ParentID. As the organization can be =
about 9 levels deep and any length... this approach doesn't accomplish =
the goal-- but I appreciate the idea.
> =20
>
Wednesday, March 7, 2012
Oracle Linked Server poor performance
I am currently querying two databases - One in SQL Server and one Oracle, to find records which are in one but not the other (essentially a reconcilliation) this is working fine in MS Access, using 2 passthrough queries to return the results of the 2 databases, then another query to find the data in one but not the other.
I decided to try the linked server approach as I thought this would give me enhanced performance - but strangely enough when I query the oracle database from MS Access the query takes about 22 seconds - whereas when I query the linked server from SQL Server the same query takes about 55 seconds.
The only difernce I can see is the passthrough query in MS Access is connecting using ODBC, an the linked server is connecting using Microsoft OLE DB Provider for Oracle.
Any ideas would be appreciated
You don't specify how you are querying the Oracle database but I would guess you are using a 4 part name query against the Oracle data source. Try changing your query to use OpenQuery instead. That is also closer to how a pass-through query in Access would execute.
-Sue
|||Thanks I will give it a go. Is using th 4 part name a bad way to query?
<edit> WOW that is much better thanks very much for your help </edit>
|||In and of itself, the four part name isn't necessarily a bad thing but you can send the query over to the server to be executed on the remote data source and force more processing, data filtering on the server. So you can push more processing off to the server. Some people have a hard time with Openquery as the flip side is that you aren't sending transact sql - you need to send the sql syntax used by the remote data source, the other database platform. I've found with Oracle data sources, it's generally better to just use Openquery.
-Sue
|||Sue - thanks very much you have been very helpful. My query now runs in under 15 seconds now.
Subscribe to:
Posts (Atom)