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.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

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
>

ORDER BY DESC

I want to select 3 columns so that the result set is sorted descending on
each column. But this doesn't seem to get me the results that I want:
SELECT col1, col2, col3
FROM tbl1
ORDER BY col1, col2, col3 DESC
And this does not work (syntax error):
...ORDER BY col1 DESC, col2 DESC, col3 DESC
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave MustaneMike,
This should work:
> ...ORDER BY col1 DESC, col2 DESC, col3 DESC
Can you show the actual query?
Andrew J. Kelly SQL MVP
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:urUl$OLBGHA.3164@.TK2MSFTNGP10.phx.gbl...
>I want to select 3 columns so that the result set is sorted descending on
>each column. But this doesn't seem to get me the results that I want:
> SELECT col1, col2, col3
> FROM tbl1
> ORDER BY col1, col2, col3 DESC
> And this does not work (syntax error):
> ...ORDER BY col1 DESC, col2 DESC, col3 DESC
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>|||> This should work:
Whoops! Nevermind, you are correct. One of the commas in the order by
clause was acidentally a decimal.
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane

ORDER BY CASe

I am creating a stored procedure to return a sorted list
Currently like this
DECLARE @.SORT tinyint
SET @.SORT = 1 --could be anything passed in
SELECT * FROM Table
ORDER BY
CASE
WHEN 1 THEN Col1
WHEN 2 THEN Col2
...etc
Now i would like to add 'ASC' or 'DESC' like follows
CASE
WHEN 1 THEN Col1 ASC
WHEN 2 THEN Col1 DESC
...etc
But this return an error 'Incorrect syntax near the keyword 'ASC'.'
IS there a way to make this work?
Yes, but the syntax is a bit weird:
create table #a(b char(1), c char(1))
insert #a values ('a', 'z')
insert #a values ('b', 'y')
insert #a values ('c', 'x')
declare @.orderby int
set @.orderby = 1
SELECT *
FROM #a
ORDER BY
CASE WHEN @.Orderby = 1 THEN b end asc,
CASE WHEN @.Orderby = 2 THEN b end desc,
CASE WHEN @.Orderby = 3 THEN c end asc,
CASE WHEN @.Orderby = 4 THEN c end desc
"GrantMagic" <grant@.magicalia.com> wrote in message
news:O0YlBadkEHA.3356@.TK2MSFTNGP15.phx.gbl...
> I am creating a stored procedure to return a sorted list
> Currently like this
> DECLARE @.SORT tinyint
> SET @.SORT = 1 --could be anything passed in
> SELECT * FROM Table
> ORDER BY
> CASE
> WHEN 1 THEN Col1
> WHEN 2 THEN Col2
> ...etc
> Now i would like to add 'ASC' or 'DESC' like follows
> CASE
> WHEN 1 THEN Col1 ASC
> WHEN 2 THEN Col1 DESC
> ...etc
> But this return an error 'Incorrect syntax near the keyword 'ASC'.'
> IS there a way to make this work?
>
|||Brilliant, that did the job perfectly!!
Thank You!
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OJZDGddkEHA.3648@.TK2MSFTNGP09.phx.gbl...
> Yes, but the syntax is a bit weird:
>
> create table #a(b char(1), c char(1))
> insert #a values ('a', 'z')
> insert #a values ('b', 'y')
> insert #a values ('c', 'x')
> declare @.orderby int
> set @.orderby = 1
> SELECT *
> FROM #a
> ORDER BY
> CASE WHEN @.Orderby = 1 THEN b end asc,
> CASE WHEN @.Orderby = 2 THEN b end desc,
> CASE WHEN @.Orderby = 3 THEN c end asc,
> CASE WHEN @.Orderby = 4 THEN c end desc
>
> "GrantMagic" <grant@.magicalia.com> wrote in message
> news:O0YlBadkEHA.3356@.TK2MSFTNGP15.phx.gbl...
>

ORDER BY CASe

I am creating a stored procedure to return a sorted list
Currently like this
DECLARE @.SORT tinyint
SET @.SORT = 1 --could be anything passed in
SELECT * FROM Table
ORDER BY
CASE
WHEN 1 THEN Col1
WHEN 2 THEN Col2
...etc
Now i would like to add 'ASC' or 'DESC' like follows
CASE
WHEN 1 THEN Col1 ASC
WHEN 2 THEN Col1 DESC
...etc
But this return an error 'Incorrect syntax near the keyword 'ASC'.'
IS there a way to make this work?Yes, but the syntax is a bit weird:
create table #a(b char(1), c char(1))
insert #a values ('a', 'z')
insert #a values ('b', 'y')
insert #a values ('c', 'x')
declare @.orderby int
set @.orderby = 1
SELECT *
FROM #a
ORDER BY
CASE WHEN @.Orderby = 1 THEN b end asc,
CASE WHEN @.Orderby = 2 THEN b end desc,
CASE WHEN @.Orderby = 3 THEN c end asc,
CASE WHEN @.Orderby = 4 THEN c end desc
"GrantMagic" <grant@.magicalia.com> wrote in message
news:O0YlBadkEHA.3356@.TK2MSFTNGP15.phx.gbl...
> I am creating a stored procedure to return a sorted list
> Currently like this
> DECLARE @.SORT tinyint
> SET @.SORT = 1 --could be anything passed in
> SELECT * FROM Table
> ORDER BY
> CASE
> WHEN 1 THEN Col1
> WHEN 2 THEN Col2
> ...etc
> Now i would like to add 'ASC' or 'DESC' like follows
> CASE
> WHEN 1 THEN Col1 ASC
> WHEN 2 THEN Col1 DESC
> ...etc
> But this return an error 'Incorrect syntax near the keyword 'ASC'.'
> IS there a way to make this work?
>|||Brilliant, that did the job perfectly!!
Thank You!
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OJZDGddkEHA.3648@.TK2MSFTNGP09.phx.gbl...
> Yes, but the syntax is a bit weird:
>
> create table #a(b char(1), c char(1))
> insert #a values ('a', 'z')
> insert #a values ('b', 'y')
> insert #a values ('c', 'x')
> declare @.orderby int
> set @.orderby = 1
> SELECT *
> FROM #a
> ORDER BY
> CASE WHEN @.Orderby = 1 THEN b end asc,
> CASE WHEN @.Orderby = 2 THEN b end desc,
> CASE WHEN @.Orderby = 3 THEN c end asc,
> CASE WHEN @.Orderby = 4 THEN c end desc
>
> "GrantMagic" <grant@.magicalia.com> wrote in message
> news:O0YlBadkEHA.3356@.TK2MSFTNGP15.phx.gbl...
> > I am creating a stored procedure to return a sorted list
> >
> > Currently like this
> >
> > DECLARE @.SORT tinyint
> > SET @.SORT = 1 --could be anything passed in
> >
> > SELECT * FROM Table
> > ORDER BY
> >
> > CASE
> > WHEN 1 THEN Col1
> > WHEN 2 THEN Col2
> > ...etc
> >
> > Now i would like to add 'ASC' or 'DESC' like follows
> >
> > CASE
> > WHEN 1 THEN Col1 ASC
> > WHEN 2 THEN Col1 DESC
> > ...etc
> >
> > But this return an error 'Incorrect syntax near the keyword 'ASC'.'
> >
> > IS there a way to make this work?
> >
> >
>

ORDER BY

hi,

i' ve Drop Down List with sorted catagory and Data Grid that cange according to selected item in drop down list ... i need to send the selected item as value to SELECT statment, so i 've send (option) as a value

"SELECT [userstory].* FROM [userstory] WHERE ([userstory].[rel_id] = @.rel_id) ORDER BY @.options "

but there is an error:

The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name

You should order by a column name and not a param. Soif your field is payDate in your DB then you would do order by payDate .|||

thanks for answering

bt if i need to use variable as ordered by coz i've send for 1st time order by status 2nd order by risk .. an so on..

can i?