Order by?

Hi All,
I have a question in sql...
How can i sort a select statement depending on nvarchar not on Int ??
My select statement is :
" select * from table1 order by st_name asc"
can anyone help me?
thanks a lot

Hello,
Sql statement "select * from table1 order by st_name" will work.
It doesnot matter whether field is nvarchar or int.
It will work for both.
|||

The database doesn't care what data type it is, just the field name to sort by. Of course, a number stored as nvarchar will sort differently than of type int, so that is a disadvantage if that is your situation.

|||What is the datatype for the fieldst_name?
If the datatype is anvarchar, it will order by alpha-numeric, ifASC (ascending)
For example:
- 101 ways
- 20 cars
- 20 boats
- apple
- boy
and of course int datatype is numerical order

sql

ORDER BY, CASE, with multiple columns

I'm unable to specify multiple columns in my order by statement if i use a case statement.
Does anyone know why this is, or what syntax would make this work?

Thanks

SELECT ...
ORDER BY (CASE Lower(@.SortExpression)
WHEN 'prodname' THEN prodname, prodprice
WHEN 'prodsize' THEN prodsize, prodname
WHEN 'prodprice' THEN prodprice, prodname
Else prodcompany, prodname
END)

Also, i realize that in your order by statement, when you use CASE, all of your columns have to be the same data type.

So in ORDER BY clause above, i am attempting to order by "prodprice" as one of the possibilities. This produces the error:Error converting data type varchar to numeric.

The whole IDEA of a case statement is to avoid opening yourself to injection attacks by Dynamic Execution.

So...how can you use the case statement to order by multiple columns, and to order with different datatypes?|||Not sure it this helps butview post 386101 discusses something close to your question. Maybe the method discussed near the bottom can be adapted.|||Right, so you'd have something like this:

SET @.SortExpression = Lower(@.SortExpression)
SELECT ...
ORDER BY
CASE WHEN @.SortExpression = 'prodname' THEN prodname END,
CASE WHEN @.SortExpression = 'prodname' THEN prodprice END,
CASE WHEN @.SortExpression = 'prodsize' THEN prodsize END,
CASE WHEN @.SortExpression = 'prodsize' THEN prodname END,
CASE WHEN @.SortExpression = 'prodprice' THEN prodprice END,
CASE WHEN @.SortExpression = 'prodprice' THEN prodname END,
prodcompany,
prodname

I'm not exactly sure what that'll do to performance. It'd be worth it to see what the execution plan says.

Terri|||that alleviated some of my problem. Thanks.

How exactly do i test the excecution plan? or check to see if it's compiling all the way?

Can i use a SQL Trace?|||Check out this article:SQL Server Query Execution Plan Analysis.

Terri

Order By statement name begining with A

Hello

Can anybody tell me the correct MS SQL statement to display names: Where each name displayed is starting with a certain letter of the alphabet only. For example:

The list should show names starting with the letter A

(what must I change in my statement below)

SELECT LastN, FirstN, Tel
FROM People_DB
WHERE LastN = 'A'
ORDER BY LastN ASC

Last Name

Adams,

Andrews,

Archer,

Arrow

Thanks

Lynn

Use the LIKE operator with the % wildcard, like this:

SELECT LastN, FirstN, Tel
FROM People_DB
WHERE LastNLIKE 'A%'
ORDER BY LastN ASC

You can also use an underscore to match a single character. Check out the LIKE clause in BOL for all the options.

Don

|||

Hi Don

Thanks for the speedy reply and the information.

Lynn

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 question

How do a construct a select statement that will perform an 'order by ASC',
but will place my nulls at the end of the list instead of the beginning of
the list.
Thanks.
Archerbagman3rd wrote on Wed, 11 May 2005 08:24:01 -0700:

> How do a construct a select statement that will perform an 'order by ASC',
> but will place my nulls at the end of the list instead of the beginning of
> the list.
What's the largest value you expect to see in the column? Place something
larger in the following to replace <<value to use>>
ORDER BY COALESCE(<column>,<<value to use>> )
eg. if column A will have a max value of 100, you would use
ORDER BY COALESCE(A,101)
Dan|||Figure out the "highest" possible value that can be in that column, dependin
g
on what datatype the column is, say it's varchar(6), then it would be
'ZZZZZZ', if it's a smallInt then its 32768, etc.
Then use the Coalesce function
Order By Coalesce(ColNAme, <MaxColValue> )
"bagman3rd" wrote:

> How do a construct a select statement that will perform an 'order by ASC',
> but will place my nulls at the end of the list instead of the beginning of
> the list.
> Thanks.
> Archer|||Use a CASE expression in the ORDER BY clause.
Example:
use northwind
go
select
OrderID,
ShippedDate
from
dbo.orders
order by
case when ShippedDate is null then 1 else 0 end asc,
ShippedDate asc
AMB
"bagman3rd" wrote:

> How do a construct a select statement that will perform an 'order by ASC',
> but will place my nulls at the end of the list instead of the beginning of
> the list.
> Thanks.
> Archer

ORDER BY problem with CONVERT

Hi,
I just realized that when I started using the CONVERT function on my dates in my SELECT statement and try to ORDER BY one of the date fields that I convert, the order isn't actually correct. Here's the statement:

$query = "SELECT id, BroSisFirstName, BroSisLastName, TerrNumber, IsCheckedOut, IsLPCheckedOut, BroSisFirstNameLP, BroSisLastNameLP, CONVERT(char(10),CONVERT(datetime, CAST(checkedOutDate as varchar(12))),101) AS checkedOutDate, CONVERT(char(10),CONVERT(datetime, CAST(returnedDate as varchar(12))),101) AS returnedDate, CONVERT(char(10),CONVERT(datetime, CAST(lpcheckedOutDate as varchar(12))),101) AS lpcheckedOutDate, CONVERT(char(10),CONVERT(datetime, CAST(lpReturnedDate as varchar(12))),101) AS lpReturnedDate FROM Checkouts WHERE IsClosed < 1 ORDER BY checkedOutDate";

It's almost as if it's treating the date as a string. Does anybody know why, and how I can correct the issue? I need to use the CONVERT function because I don't want the whole 00:00:00 returned with each date. And I say it's the CONVERT function because if I take off the CONVERT on one of the fields such as checkedOutDate and try to sort by it, it sorts correctly.try changing your sql so that your select convert(...) as xyz uses different names...

eg. if you are converting checked_out_date don't select it as checked_out_date, try selecting it as checked_out_date1.

Alternatively include the order by date as another field in your query and don't convert it. You will need to select it as something else check order_checked_out_date and then sort on that field.

I don't know if it will work, but it's worth a try.|||Well, I tried your first idea, but it didn't work, but don't quite understand your second idea - I'm new to SQL so I was wondering if you could elaborate your explanation? Thanks|||does this help??

$query = "SELECT id, BroSisFirstName, BroSisLastName, TerrNumber,
IsCheckedOut, IsLPCheckedOut, BroSisFirstNameLP, .... etc .... CAST(lpReturnedDate as varchar(12))),101) AS lpReturnedDate, checkedOutDate as order_date FROM Checkouts WHERE IsClosed < 1 ORDER BY order_date";|||That did it! Thanks! Sorry for being so lame - like I said before, I'm just a newbie.|||no worries, it's not always easy to figure out what other people mean when you are swapping emails etc...

I'm not sure if that is the best way to do things, but I'm glad it worked. :)|||I'm surprised his original query did not sort correctly, even if he did use the same name as an alias. :confused:

I wonder if it would have worked if he had just fully qualified the field in the SORT statement:

"ORDER BY Checkouts.checkedOutDate"|||possible.... not sure to be honest... it kinda surprised me as well but then (no offense meant) it is a MS product and their behaviour can be a bit perverse. ;)|||I wonder if it has something to do with the interface he is using? It doesn't look like he's executing through query analyzer or a stored proc. Perhaps something is doing some independent interpretation of his code before it is sent to the server?

Hmmm...|||Well, what you said went over my head blindman, but if it helps, I'm just using PHP on Windows XP Pro w/Apache web server, and I'm executing my query through my PHP scripts.|||Now you are over my head.

It might be worth checking Current Activity Process Info in Enterprise Manager to see exactly what statement is being sent to SQL server.

Whatever works, I guess!|||Ok, I'll look for that and check it out - thanks.sql

Order By madness

I have a simple case statement which changes the "ORDER BY" clause based on
a parameter passed to a stored proc. 4 out of 5 cases work, but one fails
with the following:
Server: Msg 295, Level 16, State 3, Procedure pr_reportPartnersDBTest, Line
12
Syntax error converting character string to smalldatetime data type.
If I comment out the case block and put each ORDER BY as an independent
statement (uncommenting one at a time), they all work without error.
When the case block is used and the int 4 is passed (my zip field), I
receive the error message above. If I comment out the case statement and
just use "ORDER BY zip" it works fine.
I must be missing something obvious, but I'm too close and suspect I'm
missing something that someone else will immediately see.
All comments, ideas or thoughts welcomed.
-- CODE snippet --
select ...
from ...
where...
order by
case @.orderBy
when 1 then dateRegistered
when 2 then gender
when 3 then bodyType
when 4 then zip
when 5 then ageRange
else dateRegistered
end
-- The next lines are for debugging purposes...
-- I commented out the case block above, then tested each of the lines below
one at a time. They all work as expected. But the case statement above fails
when I pass it a 4 (zip). All other values work as expected.
-- order by dateRegistered
-- order by gender
-- order by bodyType
-- order by zip
-- order by ageRangeCASE in SQL Server is an expression that returns a scalar value of a
specific datatype. That means that if the separate expressions for each WHEN
have a different datatype, they will explicitly be converted to the datatype
with the highest datatype precedence, in your case smalldatetime. And you
can't convert all zipcodes into a smalldatetime value.
So the simplest way to do it is to have a separate CASE for each column:
order by
case @.orderBy when 2 then gender end,
case @.orderBy when 3 then bodyType end,
case @.orderBy when 4 then zip end,
case @.orderBy when 5 then ageRange end,
case when @.OrderBy NOT IN (2,3,4,5) then dateRegistered end
Jacco Schalkwijk
SQL Server MVP
"Don B" <DonBaarns@.hotmail.com> wrote in message
news:OlsEGQOUFHA.1432@.TK2MSFTNGP09.phx.gbl...
>I have a simple case statement which changes the "ORDER BY" clause based on
> a parameter passed to a stored proc. 4 out of 5 cases work, but one fails
> with the following:
> Server: Msg 295, Level 16, State 3, Procedure pr_reportPartnersDBTest,
> Line
> 12
> Syntax error converting character string to smalldatetime data type.
> If I comment out the case block and put each ORDER BY as an independent
> statement (uncommenting one at a time), they all work without error.
> When the case block is used and the int 4 is passed (my zip field), I
> receive the error message above. If I comment out the case statement and
> just use "ORDER BY zip" it works fine.
> I must be missing something obvious, but I'm too close and suspect I'm
> missing something that someone else will immediately see.
> All comments, ideas or thoughts welcomed.
> -- CODE snippet --
> select ...
> from ...
> where...
> order by
> case @.orderBy
> when 1 then dateRegistered
> when 2 then gender
> when 3 then bodyType
> when 4 then zip
> when 5 then ageRange
> else dateRegistered
> end
> -- The next lines are for debugging purposes...
> -- I commented out the case block above, then tested each of the lines
> below
> one at a time. They all work as expected. But the case statement above
> fails
> when I pass it a 4 (zip). All other values work as expected.
>
> -- order by dateRegistered
> -- order by gender
> -- order by bodyType
> -- order by zip
> -- order by ageRange
>|||It is also worth mention that a CASE is not allowed in the ORDER BY in
Standard SQL-92. You can put it in a column in the select list, name
it and use that name in the ORDER BY. This way your code will port, be
readable to people who do not speak T-SQL dialect and your user will be
able to tell what the sort was done on, instead of trying to guess.
In the olde days, we would print the sort key at both the left and
right sides of a print out, so you could lay a ruler acorss the 132
column page. The printout were pretty fuzzy at times and this really
helped. Today, I assume that the sort column would be on the right and
locked so you can scroll horizonally.|||Jacco,
EXCELLENT... I clearly don't know enough fundamentals about T-SQL. That
works great for my example.
I would really like a secondary sort on zip code (a varchar in my case)
which would be something like this:
order by zip, dateRegistered
and/or
order by bodyType, zip
The two statements above work as expected.
I can't just put the two columns with a comma inside the Then/End
combination as the case statement uses the comma as a delimiter. I don't
know how to escape it (or the equivalent) so the case statement can return a
set of columns for the order by clause. I may also want to change the
ASC/DESC order on specific columns but I assume if I figure out the syntax
for the multiple columns it will be similar if I add specific sort orders
for some columns.
All ideas welcomed!
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:O0KSlYOUFHA.2892@.TK2MSFTNGP14.phx.gbl...
> CASE in SQL Server is an expression that returns a scalar value of a
> specific datatype. That means that if the separate expressions for each
WHEN
> have a different datatype, they will explicitly be converted to the
datatype
> with the highest datatype precedence, in your case smalldatetime. And you
> can't convert all zipcodes into a smalldatetime value.
> So the simplest way to do it is to have a separate CASE for each column:
> order by
> case @.orderBy when 2 then gender end,
> case @.orderBy when 3 then bodyType end,
> case @.orderBy when 4 then zip end,
> case @.orderBy when 5 then ageRange end,
> case when @.OrderBy NOT IN (2,3,4,5) then dateRegistered end
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Don B" <DonBaarns@.hotmail.com> wrote in message
> news:OlsEGQOUFHA.1432@.TK2MSFTNGP09.phx.gbl...
on
fails
>|||Use the same approach:
order by
case @.orderBy when 1 then zip end,
case @.orderBy when 1 then dateRegistered end,
case @.orderBy when 2 then bodyType end,
case @.orderBy when 2 then zip end ASC,
case @.orderBy when 3 then bodyType end,
case @.orderBy when 3 then zip end DESC
Terri Morton
MVP - ASP/ASP.NET
"Don B" <DonBaarns@.hotmail.com> wrote in message
news:OrWlLtOUFHA.3716@.TK2MSFTNGP12.phx.gbl...
> Jacco,
> EXCELLENT... I clearly don't know enough fundamentals about T-SQL. That
> works great for my example.
> I would really like a secondary sort on zip code (a varchar in my case)
> which would be something like this:
> order by zip, dateRegistered
> and/or
> order by bodyType, zip
> The two statements above work as expected.
> I can't just put the two columns with a comma inside the Then/End
> combination as the case statement uses the comma as a delimiter. I don't
> know how to escape it (or the equivalent) so the case statement can return
> a
> set of columns for the order by clause. I may also want to change the
> ASC/DESC order on specific columns but I assume if I figure out the syntax
> for the multiple columns it will be similar if I add specific sort orders
> for some columns.
> All ideas welcomed!
>
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote
> in message news:O0KSlYOUFHA.2892@.TK2MSFTNGP14.phx.gbl...
> WHEN
> datatype

ORDER BY in subquery of a UNION fails ??

Hi all,

I have the following UNION ALL statement that is my attempt to gather data for the past 5 weekdays (adding a "dummy" row for today's data).

I want the final output to end up in descending order, so for today, I would want today first, then Tuesday, then Monday, then Friday, then Thursday (provided there is data for each sequential day - if not, you get the idea, I want to select back to get the latest 5 days, most recent to oldest).

This select fails, because it doesn't like the ORDER BY in the subqueryselect
CASE
WHEN DATENAME(dw, GETDATE()) = 'Monday' THEN
'MON'
WHEN DATENAME(dw, GETDATE()) = 'Tuesday' THEN
'TUES'
WHEN DATENAME(dw, GETDATE()) = 'Wednesday' THEN
'WED'
WHEN DATENAME(dw, GETDATE()) = 'Thursday' THEN
'THUR'
WHEN DATENAME(dw, GETDATE()) = 'Friday' THEN
'FRI'
END AS Dow, 'N/A' AS Freight
UNION ALL
(select top 4
CASE
WHEN DATENAME(dw, [OrderDate]) = 'Monday' THEN
'MON'
WHEN DATENAME(dw, [OrderDate]) = 'Tuesday' THEN
'TUES'
WHEN DATENAME(dw, [OrderDate]) = 'Wednesday' THEN
'WED'
WHEN DATENAME(dw, [OrderDate]) = 'Thursday' THEN
'THUR'
WHEN DATENAME(dw, [OrderDate]) = 'Friday' THEN
'FRI'
END as DOW,
CAST(CONVERT(int, (Freight * 100)) as VARCHAR(10)) as Freight
from Northwind.dbo.orders where employeeid = 9 order by [OrderDate] desc )

I know you can't use an ORDER BY in a subquery, UNLESS the subquery also uses a TOP n (which this one does)...but does anyone know why this isn't liking my code?

I got the select to work the way I want it to by doing the following (really UGLY) code...SELECT U.DOW, U.Freight FROM
((select
GETDATE() as [OrderDate],
CASE
WHEN DATENAME(dw, GETDATE()) = 'Monday' THEN
'MON'
WHEN DATENAME(dw, GETDATE()) = 'Tuesday' THEN
'TUES'
WHEN DATENAME(dw, GETDATE()) = 'Wednesday' THEN
'WED'
WHEN DATENAME(dw, GETDATE()) = 'Thursday' THEN
'THUR'
WHEN DATENAME(dw, GETDATE()) = 'Friday' THEN
'FRI'
END AS Dow, 'N/A' AS Freight )
UNION ALL
(select h.OrderDate as [OrderDate], h.DOW, h.Freight FROM
(select top 4
[OrderDate] as [OrderDate],
CASE
WHEN DATENAME(dw, [OrderDate]) = 'Monday' THEN
'MON'
WHEN DATENAME(dw, [OrderDate]) = 'Tuesday' THEN
'TUES'
WHEN DATENAME(dw, [OrderDate]) = 'Wednesday' THEN
'WED'
WHEN DATENAME(dw, [OrderDate]) = 'Thursday' THEN
'THUR'
WHEN DATENAME(dw, [OrderDate]) = 'Friday' THEN
'FRI'
END as DOW,
CAST(CONVERT(int, (Freight * 100)) as VARCHAR(10)) as Freight
from Northwind.dbo.orders where employeeid = 9 order by [OrderDate] desc ) H)) U
order by OrderDate descbut am still confounded about why my original sub-select is rejected with such impunity.

My confusion seems likely related to understanding the set theory or basic concepts of the building of the select/Union rather than the way I am using the ORDER BY syntax, but I just can't seem to explain it to myself.

Thoughts?
Thanks!EDIT: Oops - retracted.|||dang, and I had my blistering rebuttal all ready ;)|||Damn that sucks. I was about to post that your place holder does not have an orderdate but neither does the one that works. Going to have to look at this when I get home.|||OMG NOOOOOO!!! Not working on SQL at HOME!! I wouldn't want to be responsible for THAT in any way, shape or form!

Home is for Beer, relaxation, Lovin', and...well...pretty much ANYTHING but working on Paul's SQL questions ;)

...and what do you mean by "placeholder", BTW?|||please explain what "isn't liking my code" means

is there an error message?

without it, we're just guessing

there are other ways of getting the last 4 order dates, by the way, but they involve a self-join or yet another subselect|||Server: Msg 156, Level 15, State 1, Line 29
Incorrect syntax near the keyword 'order'.Yeah, This is not probably the way I woulda done it...

Had a peer at work come to me with this, and ask me why the order-by in the sub-query doesn't work. I could not explain it, so I just banged my head against it until I figured out a way. Then posted here my ugly code just to show what worked. And so anyone so motivated could run the select to see what I am after for output.

Of course a coupla decades in software development have taught me that you are indeed right...more'n one way to skin a catfish. I am of course open to other alternatives, though am not sure off the top of my head that a self-join would be any less ugly (especially when this sub-select theoretically SHOULD work). Of course, I am always learning, and not typically arrogant enough to think my way is the only way :) (though mind you , I am in no way adverse to steppin up to arm-wrestle about it being the RIGHT way :D )

If you want to take the time to post other ways or improvements, I am ALWAYS open to such nudges or downright slapdowns - in fact I LOVE 'em.

I always appreciate it when someone sees thier way clear to burn a few brain cells on my behalf.|||let's start by replacing that hideous CASE expression

select upper(left(datename(dw,getdate())
,3+datepart(dw,getdate())%2)) as DOW|||i'm sorry, i just discovered i don't have northwind to play with

i'll see if i can squeeze this in tomorrow at the job|||since she's been gone it's all work,exercise study sleep. occasionally I go out with the boys, but my first instinct was right.

in the first one that does not work there is no order date in the first half of your union and in the second one you alias GETDATE() as your order date. my brain was just too mushy earlier.|||Hmmmm...if I understand you correctly, I think that is my intent (which doesn't mean SQL will let me do what I think I want to *L*)

I am only trying to ORDER BY the second select in the UNION...that is because the table used in the second union has daily data in it since about June of 1963 (aka, many more than the 4 dates I am interested in). So the second part of the union is simply trying to grab the last 4 dates (most recent) which is why I am trying to use the TOP 4...ORDER BY - - to get just the last 4 dates in table).

My intent in the first (non-working) UNION is to put my "hard-coded" row of TODAY's data in, then add to it the latest 4 days from the table in the second select of the UNION.

I also want the whole thing ordered by date, which is missing from the first UNION, but that part shouldn't matter to me, should it? At least relative to the pulling of the 5 rows I want. The second (working, ugly) select DOES have the overall ORDER BY in it, which probably causes some confusion relative to my question.

I am just trying to figure out why the initial (non-working) select complains of a syntax error, when to my way of thinking, I should be able to put an idependent select in the second half of the UNION and apply the ORDER BY only to that sub-select (it would, again, be a seperate issue to order the whole result of the UNION).

In a nutshell, if I run the second select in the first UNION by itself, it works fine. But when I try to run the whole UNION select, it fails with the previously-noted error. Shouldn't my sub-select work within the UNION if I place it inside parenthesis as I have?

Part of the problem may be that I am not doing a good job of explaining specifically what I think should work *LOL* - - or not understanding your explaination of why it WON'T.

As always, thanks for expending brain cell activity on my behalf.|||the whole UNION is one result set and the order by applies to the whole result set.|||here, this works:select *
from (
select top 4
[OrderDate]
, substring('SUN MON TUESWED THURFRI SAT '
, 4*datepart(dw,[OrderDate])-3,4) as DOW
, right(space(10)+CAST(Freight*100 as VARCHAR(10)),10) as Freight
from Northwind.dbo.orders
where employeeid = 9
order by [OrderDate] desc
) as dt
union all
select getdate()
, substring('SUN MON TUESWED THURFRI SAT '
, 4*datepart(dw,getdate())-3,4) as DOW
, ' N/A' AS Freight|||he already had one that works. he was just wondering why the other did not. but yes yours is prettier.|||AH-HAA!!!!!

I KNEW it was a syntax assumption...

FROM BOL:
The ORDER BY clause can include items not appearing in the select list. However, if SELECT DISTINCT is specified, or if the SELECT statement contains a UNION operator, the sort columns must appear in the select list.

MY problem was that I was trying to FORCE the ORDER BY in my sub-query to work like a NON-UNION sub-query, and could not figure out why my sub-query (with order by) would not function as an independent sub-query (as it would in a JOIN, for example).

And you guys, on a different plane *L*, assumed that I was playing by the taken-for-granted UNION rules understanding.

I KNEW it was gonna be something like that.

Sincere thanks for your patience and willingness to work with me on this. When I cannot understand why something doesn't work when I am playing by the rules, it often turns out that I am not playing by the rules ;)

...either that, or God is just toying with me at the time...

In this case it was the former.

Thanks for trying to explain when I refused to listen - y'all are now in the same fine group as my parents, teachers, and...well, pretty much everyone else I know :D|||in the first one that does not work there is no order date in the first half of your union and in the second one you alias GETDATE() as your order date. my brain was just too mushy earlier.

this is what i was saying, right?|||Yeah, that's what I mean though...I was thinking differently, so even though you posted that, I thought "huh? I don't WANT a date in the first part of the UNION", because I didn't need it in the final output.

At that time I didn't yet realize/know that it was REQUIRED to make the UNION work.

Theoretically, I shouldn't NEED it (SQL Server notwithstanding) because the output of various selects in the UNION should (logically, or non-logically ;) ) be independent of each other, right? From a set theory-type perspective, anyway.

My second select (the one that worked) only ACCIDENTALLY unbroke the rules that got me in the first one.

Thanks regardless, Sean...I really shouldn't let anyone know any more about my thought process than I have to ;)

Order By in a View

Hi everyone,
I'm relatively new to MSSQL.
I was trying to import a view from MS Access to MSSQL that has an order by statement.

And everytime I tried it gave me the following error:
"The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified."

ِAnyone can help?

M.

Quote:

Originally Posted by mmaamoun

Hi everyone,
I'm relatively new to MSSQL.
I was trying to import a view from MS Access to MSSQL that has an order by statement.

And everytime I tried it gave me the following error:
"The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified."

ِAnyone can help?

M.

option 1. place the ORDER BY on the code where you call the view

option 2. include a top 100% on your view. or use a ridiculous large number that will ensure return of all rows|||Excellent, the select top 100 percent ...
worked like magic for me:)

Thanks:)

Quote:

Originally Posted by ck9663

option 1. place the ORDER BY on the code where you call the view

option 2. include a top 100% on your view. or use a ridiculous large number that will ensure return of all rows

ORDER BY error

Hello,

in a complex stored procedure, i get an error when i want to apply an ORDER BY statement :

Here is a part of this Stored Procedure :

SELECT TaOperations.Id_Operation, TaOperations.HeureDebut, TaOperations.Signe, TaOperations.Facturer, TaOperations.IdUserVerrou,

TaOperations.Id_Client, TaTypeOperation.Facturable, CONVERT(CHAR(10), TaOperations.HeureDebut,

103) + ' - ' + TaTypeOperation.TypeOperation AS Code

FROM TaOperations INNER JOIN

TaTypeOperation ON TaOperations.Id_TypeOperation = TaTypeOperation.Id_TypeOperation

WHERE (TaOperations.Id_Client = CONVERT(int, @.Id_Client)) AND (TaTypeOperation.Facturable = 1) AND (TaOperations.IdUserVerrou IS NULL)

If I want to apply an ORDER BY statement, i have

SELECT TaOperations.Id_Operation, TaOperations.HeureDebut, TaOperations.Signe, TaOperations.Facturer, TaOperations.IdUserVerrou,

TaOperations.Id_Client, TaTypeOperation.Facturable, CONVERT(CHAR(10), TaOperations.HeureDebut, 103)

+ ' - ' + TaTypeOperation.TypeOperation AS Code

FROM TaOperations INNER JOIN

TaTypeOperation ON TaOperations.Id_TypeOperation = TaTypeOperation.Id_TypeOperation

WHERE (TaOperations.Id_Client = CONVERT(int, @.Id_Client)) AND (TaTypeOperation.Facturable = 1) AND (TaOperations.IdUserVerrou IS NULL)

ORDER BY TaOperations.HeureDebut

I get this error :

Msg 156, Level 15, State 1, Procedure SIE_ListeOperationsFacturablesParClient, Line 46

Incorrect syntax near the keyword 'ORDER'.

Does anyone cab help me ?

Is there anything else after the SELECT statement?

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Here is the whole code :

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[SIE_ListeOperationsFacturablesParClient]

@.Id_Client char(5),

@.Cas int

AS

BEGIN

SET NOCOUNT ON;

If @.cas = 0 (

SELECT TaOperations.Id_Operation, TaOperations.HeureDebut, TaOperations.Signe, TaOperations.Facturer, TaOperations.IdUserVerrou,

TaOperations.Id_Client, TaTypeOperation.Facturable, CONVERT(CHAR(10), TaOperations.HeureDebut,

103) + ' - ' + TaTypeOperation.TypeOperation AS Code

FROM TaOperations INNER JOIN

TaTypeOperation ON TaOperations.Id_TypeOperation = TaTypeOperation.Id_TypeOperation

WHERE (TaOperations.Id_Client = CONVERT(int, @.Id_Client)) AND (TaTypeOperation.Facturable = 1) AND (TaOperations.Signe = 1) AND

(TaOperations.IdUserVerrou IS NOT NULL))

If @.cas = 1 (

SELECT TaOperations.Id_Operation, TaOperations.HeureDebut, TaOperations.Signe, TaOperations.Facturer, TaOperations.IdUserVerrou,

TaOperations.Id_Client, TaTypeOperation.Facturable, CONVERT(CHAR(10), TaOperations.HeureDebut,

103) + ' - ' + TaTypeOperation.TypeOperation AS Code

FROM TaOperations INNER JOIN

TaTypeOperation ON TaOperations.Id_TypeOperation = TaTypeOperation.Id_TypeOperation

WHERE (TaOperations.Id_Client = CONVERT(int, @.Id_Client)) AND (TaTypeOperation.Facturable = 1) AND (TaOperations.Signe = 0) AND

(TaOperations.IdUserVerrou IS NOT NULL))

If @.cas = 2 (

SELECT TaOperations.Id_Operation, TaOperations.HeureDebut, TaOperations.Signe, TaOperations.Facturer, TaOperations.IdUserVerrou,

TaOperations.Id_Client, TaTypeOperation.Facturable, CONVERT(CHAR(10), TaOperations.HeureDebut, 103)

+ ' - ' + TaTypeOperation.TypeOperation AS Code

FROM TaOperations INNER JOIN

TaTypeOperation ON TaOperations.Id_TypeOperation = TaTypeOperation.Id_TypeOperation

WHERE (TaOperations.Id_Client = CONVERT(int, @.Id_Client)) AND (TaTypeOperation.Facturable = 1) AND (TaOperations.IdUserVerrou IS NULL)

ORDER BY TaOperations.HeureDebut DESC

)

END

|||T-SQL normally uses the following syntax but you can leave out the BEGIN and END if you are only executing one query (or a single line of code).

IF @.var = x
BEGIN
YOUR CODE
END

Could you try leaving out the ( and ) for the if clauses?

If @.cas = 2

SELECT TaOperations.Id_Operation, TaOperations.HeureDebut, TaOperations.Signe, TaOperations.Facturer, TaOperations.IdUserVerrou,

TaOperations.Id_Client, TaTypeOperation.Facturable,CONVERT(CHAR(10), TaOperations.HeureDebut, 103)

+' - '+ TaTypeOperation.TypeOperation AS Code

FROM TaOperations INNERJOIN

TaTypeOperation ON TaOperations.Id_TypeOperation = TaTypeOperation.Id_TypeOperation

WHERE(TaOperations.Id_Client =CONVERT(int, @.Id_Client))AND(TaTypeOperation.Facturable = 1)AND(TaOperations.IdUserVerrou ISNULL)

ORDERBY TaOperations.HeureDebut DESC

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||Wonderful !!! it works thanks a lotsql

ORDER BY Command in SQL Stored Procedure

How do you use a variable in the ORDER BY command in a sql statement.
I currently have:
****************************************
CREATE Procedure SS_POList
(
@.CompanyID nvarchar(10),
@.PO varchar (20) = '%'
)
As

SELECT
SS_Sendback.EndUserPO,
SS_Sendback.PO,
COUNT(SS_Sendback.EndUserPO) as pocount,
SUM(SS_Sendback.Customerprice) as totcost

FROM
SS_Sendback

WHERE
SS_Sendback.EndusercustomerNumber = @.CompanyID
AND
SS_Sendback.EnduserPO Like @.PO

GROUP BY
SS_Sendback.EndUserPO,
SS_Sendback.PO

ORDER BY
SS_Sendback.PO
GO
*************************************
I changed it to
*************************************
CREATE Procedure SS_POList
(
@.CompanyID nvarchar(10),
@.PO varchar (20) = '%',
@.Order varchar(20)
)
As

SELECT
SS_Sendback.EndUserPO,
SS_Sendback.PO,
COUNT(SS_Sendback.EndUserPO) as pocount,
SUM(SS_Sendback.Customerprice) as totcost

FROM
SS_Sendback

WHERE
SS_Sendback.EndusercustomerNumber = @.CompanyID
AND
SS_Sendback.EnduserPO Like @.PO

GROUP BY
SS_Sendback.EndUserPO,
SS_Sendback.PO

ORDER BY
@.Order
GO

and I receive the following error..
error1008: 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.
How does it know @.order is a position and not a column name... It's a variable. I'm obviously doing something wrong. Can someone help.
Thanksas you've discovered, the order by cannot be dynamic. you'd have to use a CASE statement to use a variable here - pseudocode

CASE @.orderBy = 'column1'
ORDER BY column1

that kinda thing. I've seen an FAQ on this somewhere, I'll see if I can dig out the link.|||I'd like to see the FAQ if you can find it. I tried looking but I had no luck.|||try this one for size :

http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=344|||Rather than the method suggested in the article:

declare @.OrderBy varchar(20)
select @.Orderby = 'ContactName'
select * from Northwind.dbo.Customers
ORDER BY
 CASE @.OrderBy
 WHEN 'CompanyName' THEN CompanyName
 WHEN 'ContactName' THEN ContactName
 END
 ASC

I suggest this method:

declare @.OrderBy varchar(20)
select @.Orderby = 'ContactName'
select * from Northwind.dbo.Customers
ORDER BY
 CASE WHEN @.OrderBy ='CompanyName' THEN CompanyName END ASC,
 CASE WHEN @.OrderBy ='ContactName' THEN ContactName END ASC

The method in the article requires all of the columns to be of the same datatype (or CAST as the same datatype). The alternate method allows you to use different data types easily, plus use ASC and DESC as needed.

Terri|||Thanks for all you help.
This seems like a lot of work when a simple
ORDER BY @.somevariable
should work but apperently thats not supported by SQL server.
In my searches I also found some suggestions to assign the search string to a variable and execute the string but I think that would be the least desireable way to do this.
Thanks Again.

ORDER BY clause

I would like to order my select statement depends on column3 value,
something like this:
SELECT * from table
ORDER BY column1,column2,case when column3=1 then column4,column5 else
column5,column4 end
Any idea?
Regards,SYou're almost there:
SELECT * from table
ORDER BY
column1
, column2
, case when column3=1 then column4 else column5 end
, case when column3=1 then column5 else column4 end
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"simon" <simon.zupan@.iware.si> wrote in message
news:YY2Je.1186$cE1.174161@.news.siol.net...
I would like to order my select statement depends on column3 value,
something like this:
SELECT * from table
ORDER BY column1,column2,case when column3=1 then column4,column5 else
column5,column4 end
Any idea?
Regards,S

Order By Case Cast Convert Error

I have created a SQL Stored Procedure that uses a Case statement to
determine the Order By. For one of the Case statements I am trying to
turn a Char field into Datetime in for the Order By, however I can not
get it to work. Can someone please take a look and my code below and
tell me what I am doing wrong. Thank you.

ORDER BY
CASE WHEN @.SortBy = 'dttm_stamp' THEN dttm_stamp End,
CASE WHEN @.SortBy = 'Event_Date1' THEN CAST(CONVERT(char(10),
Event_Date1,101) as datetime) End,
CASE WHEN @.SortBy = 'FullName' THEN Emp_lastname + ', ' +
Emp_firstname End,
CASE WHEN @.SortBy = 'FullName Desc' THEN Emp_lastname + ', ' +
Emp_firstname End DESC,
CASE WHEN @.SortBy = 'Emp_SSN' THEN Emp_SSN End<jguilford@.cybergroup.com> wrote in message
news:1105983724.986065.70140@.c13g2000cwb.googlegro ups.com...
>I have created a SQL Stored Procedure that uses a Case statement to
> determine the Order By. For one of the Case statements I am trying to
> turn a Char field into Datetime in for the Order By, however I can not
> get it to work. Can someone please take a look and my code below and
> tell me what I am doing wrong. Thank you.
> ORDER BY
> CASE WHEN @.SortBy = 'dttm_stamp' THEN dttm_stamp End,
> CASE WHEN @.SortBy = 'Event_Date1' THEN CAST(CONVERT(char(10),
> Event_Date1,101) as datetime) End,
> CASE WHEN @.SortBy = 'FullName' THEN Emp_lastname + ', ' +
> Emp_firstname End,
> CASE WHEN @.SortBy = 'FullName Desc' THEN Emp_lastname + ', ' +
> Emp_firstname End DESC,
> CASE WHEN @.SortBy = 'Emp_SSN' THEN Emp_SSN End

What does "can not get it to work" mean? Do you get errors, or do you get
unexpected results? The best idea would probably be to post CREATE TABLE and
INSERT statements to set up a test case which illustrates your problem.

http://www.aspfaq.com/etiquette.asp?id=5006

Simon|||On 17 Jan 2005 09:42:05 -0800, jguilford@.cybergroup.com wrote:

>I have created a SQL Stored Procedure that uses a Case statement to
>determine the Order By. For one of the Case statements I am trying to
>turn a Char field into Datetime in for the Order By, however I can not
>get it to work. Can someone please take a look and my code below and
>tell me what I am doing wrong. Thank you.

Hi jquilford,

I guess that this is the line that's giving you trouble:

>CASE WHEN @.SortBy = 'Event_Date1' THEN CAST(CONVERT(char(10),
>Event_Date1,101) as datetime) End,

You are converting the datetime variable to the american mm/dd/yyyy
format, which is not very well suited for sorting. Then you are converting
it back to datetime, running alll kinds of risks because this format is
ambiguous - it's easily misinterpreted as dd/mm/yyyy, causing either wrong
sorting or conversion errors.

If your intention is to strip the time part from the datetime column, so
you can order by the date part only, use

CASE WHEN @.SortBy = 'Event_Date1' THEN DATEADD(day, DATEDIFF(day,
'20000101', Event_Date1), '20000101')

Or, if you really want to do it by conversion to string and back, use the
safe yyyymmdd format:

CASE WHEN @.SortBy = 'Event_Date1' THEN CAST(CONVERT(char(8),
Event_Date1,112) as datetime) End,

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> Or, if you really want to do it by conversion to string and back, use the
> safe yyyymmdd format:
> CASE WHEN @.SortBy = 'Event_Date1' THEN CAST(CONVERT(char(8),
> Event_Date1,112) as datetime) End,

Or simply say:

CASE WHEN @.SortBy = 'Event_Date1'
THEN CONVERT(char(8), Event_Date1,112)
End,

Then again, we have no idea jguildford mean "I can not get it to work".

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Mon, 17 Jan 2005 22:40:11 +0000 (UTC), Erland Sommarskog wrote:

>Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
>> Or, if you really want to do it by conversion to string and back, use the
>> safe yyyymmdd format:
>>
>> CASE WHEN @.SortBy = 'Event_Date1' THEN CAST(CONVERT(char(8),
>> Event_Date1,112) as datetime) End,
>Or simply say:
> CASE WHEN @.SortBy = 'Event_Date1'
> THEN CONVERT(char(8), Event_Date1,112)
> End,

Hi Erland,

Of course - no need to change it back to datetime in this case. Thanks!

>Then again, we have no idea jguildford mean "I can not get it to work".

Maybe he (she?) will post with more details?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||1) why are you using a CASE expression in an ORDER BY? Is the
destruction of portable code one of your design goals?

2) The column used for a sort should appear in the output so a human
being can use it to search the list. Basic human factors, etc. Put it
in the SELECT list.

3) Why are you making a date into a string in the first place? You can
sort of temporal datatypes too. And why are you converting it to a
string that is not in temporal order?|||Let me try to better explain my problem. I have created a page in
asp.net that has a datagrid that pulls a few fields form a SQL Server
table. Also on this page there is a dropdown box that allows you to
pick the way the data is sorted. You can pick to sort it by the data
the record was created, name (both ascending and descending), social
security number and by the date of the event. Now here is my problem,
the Event_Date1 field is a 10 character field (00/00/0000) instead of a
datetime field, it was set up like this a long time ago and can not be
changed. Because it is a character field when you use it to sort the
data it is sorted by month then day then year instead of year then
month then day. I tried to use a Cast and Convert statement to change
it to a datetime so it would sort correctly but now when I try to sort
by Event_Date1 I receive this error: "The conversion of a char data
type to a datetime data type resulted in an out-of-range datetime
value." I have included all of the code in my stored procedure below.
I hop this clears up some of the confusion. Let me know if there is
anything else I did not clear up. Thank you.

CREATE PROCEDURE spShowArchives
@.SecurityID int,
@.SortBy varchar(50)
AS
SELECT ID, Emp_lastname + ', ' + Emp_firstname as FullName, Emp_SSN,
Event_Date1, Injury_Illness_Type, Jurisdiction, Injury_Cause_Desc,
SISCO_claim, dttm_stamp FROM omni_table
WHERE security_id = @.SecurityID
AND omni_table.deleted_flag = 0
AND (SISCO_claim <> '' or SISCO_claim <> null or SISCO_claim <>
'Submit')
ORDER BY
CASE WHEN @.SortBy = 'dttm_stamp' THEN dttm_stamp End,
CASE WHEN @.SortBy = 'Event_Date1' THEN CAST(CONVERT(char(10),
Event_Date1,101) as datetime) End,
CASE WHEN @.SortBy = 'FullName' THEN Emp_lastname + ', ' +
Emp_firstname End,
CASE WHEN @.SortBy = 'FullName Desc' THEN Emp_lastname + ', ' +
Emp_firstname End DESC,
CASE WHEN @.SortBy = 'Emp_SSN' THEN Emp_SSN End
GO|||On 18 Jan 2005 11:53:03 -0800, jagguil4d wrote:

>Let me try to better explain my problem.
(snip)
> Now here is my problem,
>the Event_Date1 field is a 10 character field (00/00/0000) instead of a
>datetime field, it was set up like this a long time ago and can not be
>changed.

Hi jagguil4d / jguilford,

This is not correct. Of course it *can* be changed. You probably meant to
write that someone in your organization doesn't *want* it to be changed.
And that someone is most likely the person who's budget is impacted by the
cost of actually imprivong your system, but doesn't suffer from the
(eventually) much higher costs of numerous workarounds, bugfixes and error
recovery.

> Because it is a character field when you use it to sort the
>data it is sorted by month then day then year instead of year then
>month then day. I tried to use a Cast and Convert statement to change
>it to a datetime so it would sort correctly but now when I try to sort
>by Event_Date1 I receive this error: "The conversion of a char data
>type to a datetime data type resulted in an out-of-range datetime
>value."

You might try if you have more luck with
CASE WHEN @.SortBy = 'Event_Date1' THEN CONVERT(datetime, Event_Date1,
101) End,

If that fails as well, you have at least one row in your table with an
invalid date (and boy, are you lucky if it is indeed only one <g>). These
can be hard to find. A good starting point would be
SELECT KeyColumn, Event_Date1
FROM omni_table
WHERE ISDATE(Event_Date1) = 0

If the above returns no rows, but you get errors converting Event_Date1 to
datetime, then you probably have rows with a date in DD/MM/YYYY format, or
some other date format. The following will hopefully catch most of these
buggers:
SELECT KeyColumn, Event_Date1
FROM omni_table
WHERE Event_Date1 >= '13'
OR Event_Date1 NOT LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]'

Good luck! (You'll need it...)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||jagguil4d (jguilford@.cybergroup.com) writes:
> Now here is my problem, the Event_Date1 field is a 10 character field
> (00/00/0000) instead of a datetime field, it was set up like this a long
> time ago and can not be changed.

Of course it can! I hear this lame excuse every time, but seriously,
yes it can be changed. Just why would it be left unchanged?

Anyway, as Hugo points out you have garabge in this column, so if
you insist on that you don't want to change it, do this:

CASE WHEN @.SortBy = 'Event_Date1' THEN
substring(Event_Date1, 7, 4) + substring(Event_Date1, 1, 2) +
substring(Event_Date1, 4, 2)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||--CELKO-- (jcelko212@.earthlink.net) writes:
> 1) why are you using a CASE expression in an ORDER BY? Is the
> destruction of portable code one of your design goals?

Because he more cares about serving his users than paying sacrifice
to the Holy Church of Portability.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> he more cares about serving his users than paying sacrifice to the
Holy Church of Portability. <<

When you can get portability for no extra cost, there is no sacrifice
and considerable gains in maintaining the code over the life of the
system. Would really use getdate() instead of CURRENT_TIMESTAMP to
save a few keystrokes? Or ISNULL() instead of COALESCE()?|||--CELKO-- (jcelko212@.earthlink.net) writes:
> When you can get portability for no extra cost, there is no sacrifice
> and considerable gains in maintaining the code over the life of the
> system. Would really use getdate() instead of CURRENT_TIMESTAMP to
> save a few keystrokes? Or ISNULL() instead of COALESCE()?

In this case you questioned the use of CASE in ORDER BY, which
jguilford had added to offer desired functionailty to his application.
So there is a cost to be portable here.

By the way, there is a situations where isnull() works, but not
coalesce().

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Order By Adding Rows?

Hi All,
Having trouble with the following SQL statement. If there is multiple
ORDER by's it adds rows to the result. The SQL statement below adds
rows to the result, if I remove block_line_no
from the orer by it returns the correct data.
SELECT *
FROM nc_block_data
WHERE (PROGRAM_ID = 2607) OR
(PROGRAM_ID = 2608) OR
(PROGRAM_ID = 2609) OR
(PROGRAM_ID = 2610) OR
(PROGRAM_ID = 2612) OR
(PROGRAM_ID = 2613) OR
(PROGRAM_ID = 2614) OR
(PROGRAM_ID = 2615) OR
(PROGRAM_ID = 2616) OR
(PROGRAM_ID = 2617) OR
(PROGRAM_ID = 2618) OR
(PROGRAM_ID = 2619) OR
(PROGRAM_ID = 2620) OR
(PROGRAM_ID = 2621)
ORDER BY program_id, block_line_noSkip wrote:
> Hi All,
> Having trouble with the following SQL statement. If there is multiple
> ORDER by's it adds rows to the result. The SQL statement below adds
> rows to the result, if I remove block_line_no
> from the orer by it returns the correct data.
>
> SELECT *
> FROM nc_block_data
> WHERE (PROGRAM_ID = 2607) OR
> (PROGRAM_ID = 2608) OR
> (PROGRAM_ID = 2609) OR
> (PROGRAM_ID = 2610) OR
> (PROGRAM_ID = 2612) OR
> (PROGRAM_ID = 2613) OR
> (PROGRAM_ID = 2614) OR
> (PROGRAM_ID = 2615) OR
> (PROGRAM_ID = 2616) OR
> (PROGRAM_ID = 2617) OR
> (PROGRAM_ID = 2618) OR
> (PROGRAM_ID = 2619) OR
> (PROGRAM_ID = 2620) OR
> (PROGRAM_ID = 2621)
> ORDER BY program_id, block_line_no
Can you post some code to reproduce that problem - CREATE TABLE
followed by INSERTs of one or two sample rows. Also tell us what
version, edition and service pack of SQL Server you are using. If your
server is patched and you can't reproduce the problem then maybe you
are looking at some corrupt data or index somewhere.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Here is the table structure. I created a test table but was not able to
reproduce the problem. Could it be something with the table structure?
USE [iFrame]
GO
/****** Object: Table [dbo].[Nc_Block_Data] Script Date: 04/11/2006
14:32:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Nc_Block_Data](
[PROGRAM_ID] [int] NOT NULL,
[BLOCK_LINE_NO] [int] NOT NULL,
[PROG_LINE_NO] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[NC_CODE] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HOLE_NAME] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NCBLOCK_STATUS] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[NCBLOCK_STATUS_DT] [datetime] NULL,
[ERROR_INF] [varchar](80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF|||Not sure why but it looks like it was bad data, just in one section of
the DB. Does this make sense? If I grab a different group of data all
works fine.|||If you can be certain that the data is not corrupt, try reindexing the
suspicious block_line_no column.
ML
http://milambda.blogspot.com/sql

Order By - Conditional

I wish to include a condition in my order by statement.
i.e. If the value of Field X > Y then Order by Field X,
else Order by Field Z.
I have tried inserting a select case but i cannot get it
to work
Here is an extract from the table
Order_ID Status Name
50 10 Michael
51 10 John
52 10 Peter
53 20 Adam
54 20 Bruce
The condition I would like to apply is as follows:
If Status is greater than 11, Order by Name, else order by
Order_ID
Any help wpuld be greatly appreciated
On Fri, 13 Aug 2004 05:04:53 -0700, Wes wrote:

>I wish to include a condition in my order by statement.
>i.e. If the value of Field X > Y then Order by Field X,
>else Order by Field Z.
>I have tried inserting a select case but i cannot get it
>to work
>Here is an extract from the table
>Order_ID Status Name
>50 10 Michael
>51 10 John
>52 10 Peter
>53 20 Adam
>54 20 Bruce
>The condition I would like to apply is as follows:
>If Status is greater than 11, Order by Name, else order by
>Order_ID
>Any help wpuld be greatly appreciated
Hi Wes,
Something like this?
ORDER BY Status,
CASE WHEN Status > 11 THEN Name END,
CASE WHEN Status <= 11 THEN Order_ID END
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||I have tried the following but with no success...
ORDER BY CASE WHEN Status > 11 THEN Name END,
CASE WHEN Status <= 11 THEN Order_ID END
Is there any other approach I can use?
Thanks in advance
[vbcol=seagreen]
>--Original Message--
>On Fri, 13 Aug 2004 05:04:53 -0700, Wes wrote:
by
>Hi Wes,
>Something like this?
>ORDER BY Status,
> CASE WHEN Status > 11 THEN Name END,
> CASE WHEN Status <= 11 THEN Order_ID END
>
>Best, Hugo
>--
>(Remove _NO_ and _SPAM_ to get my e-mail address)
>.
>
|||On Wed, 15 Sep 2004 10:14:33 -0700, Wes wrote:

>I have tried the following but with no success...
>ORDER BY CASE WHEN Status > 11 THEN Name END,
>CASE WHEN Status <= 11 THEN Order_ID END
>
>Is there any other approach I can use?
>Thanks in advance
Hi Wes,
Could you be more specific? What happened when you tried the code above?
Did you get an error? Unexpected results? Explosions in the server room?
I don't see anything wrong with the code snippet above. To goo further
into this, I must reproduce your situation on my system. To do that, you
should post more information. To be precise, I need:
1. Table structure, posted as DDL (CREATE TABLE statements; irrelevant
columns may be omitted but please do include all constraints);
2. Enough sample data to show what you want to achieve, posted as INSERT
statements;
3. The output you expect from the sample data you posted;
4. The complete SQL statement you are currently using plus the output you
are getting from it - if it's an error message, please copy and paste the
complete text;
5. A short and concise description of the business problem you're trying
to solve.
With that, I should be able to reproduce your problem and search for a
solution.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||My apologies Hugo,
the query was correct.
When building test data to send you, I noticed that the
only problemn i had was the order of the 2 case
statements. i.e. I switched the case statements
instead of ...[vbcol=seagreen]
i used ...[vbcol=seagreen]
Thanks for you help!
Wes

>--Original Message--
>On Wed, 15 Sep 2004 10:14:33 -0700, Wes wrote:
>
>Hi Wes,
>Could you be more specific? What happened when you tried
the code above?
>Did you get an error? Unexpected results? Explosions in
the server room?
>I don't see anything wrong with the code snippet above.
To goo further
>into this, I must reproduce your situation on my system.
To do that, you
>should post more information. To be precise, I need:
>1. Table structure, posted as DDL (CREATE TABLE
statements; irrelevant
>columns may be omitted but please do include all
constraints);
>2. Enough sample data to show what you want to achieve,
posted as INSERT
>statements;
>3. The output you expect from the sample data you posted;
>4. The complete SQL statement you are currently using
plus the output you
>are getting from it - if it's an error message, please
copy and paste the
>complete text;
>5. A short and concise description of the business
problem you're trying
>to solve.
>With that, I should be able to reproduce your problem and
search for a
>solution.
>Best, Hugo
>--
>(Remove _NO_ and _SPAM_ to get my e-mail address)
>.
>

order by

Hi everyone,
I have a select statement of the form
SELECT * FROM temp ORDER BY time

I have a scalar function ConvertToMinutes that takes in varchar and returns int, is there any way to do something like this SELECT * FROM temp ORDER BY ConvertToMinutes(time). I tried doing this and it doesn't work (it tells me ConvertToMinutes is not a built-in function). Please guide me as to how I would accomplish this. Thanks in advance.

P.S. Clarification: I am trying to order the table temp by the value returned by the function ConvertToMinutes on the coloumn time.I am not exactly sure if you can use function in the order by clause. try

SELECT * FROM temp ORDER BY dbo.ConvertToMinutes(convert(varchar,time))

|||

I do believe ndinakar's method will work, however, if it does not, you can also do this:

SELECT *
FROM (SELECT *,dbo.converttominutes(time) mytime FROM temp) t1
ORDER BY mytime

or this:

SELECT temp.*
FROM temp
JOIN (SELECT DISTINCT time,dbo.converttominutes(time) mytime FROM temp) t1 ON (temp.time=t1.time)
ORDER BY mytime

|||Hey guys thanks for the answers. I tried the first solution and it worked, so I did not try the second one. But thanks to both of you for answering.sql

Oracle's Merge equivalent of SQL server?

What is the equivalent of Oracle's Merge statement in SQL server ?UPDATE followed by an INSERT. There isn't a single statement to do it but
you can obviously wrap the two in a transaction if you really need to.
David Portas
SQL Server MVP
--

Oracles Analyze Table statement ?

Hi,
Below is an Oracle query used for cost optimization purpose :
analyze table test estimate statistics sample 2500 Rows;
Is there any equivalent for the above in SQL Server ?
Please advice,
Thanks,
SamWhy look for equivalent? Just convert to it!|||microsoft sql server automatically updates statistics as a default setting
you wouldnt have to manually create statistics unless you chose exceptions to the norm in your design.

for example
turning off autoupdatestats
creating composite indexes and creating statistics on the non leading columns
creating statistics on commonly searched columns that wouldnt normally warrant an index.

you can run the create statistics statement or the update statistics statements respectively

Check out Books Online in your SQL Server start menu folder
and search for statistics create stats or update stats.|||Below is an Oracle query used for cost optimization :

analyze table Customer list chained rows into CHAINED_ROWS;

Is there any equivalent for the above in SQL Server ?

Please advice,

Thanks,
Sam|||Is there any solutions?
I too need the equivalent for
the above query.

any help would be appreciated

With Regards
Sathya|||MS-SQL does not need the kind of maintenance that Oracle needs. It used to, years ago, but that time is long gone.

I could make up a number of suggestions, some of which would actually do something to improve optimization a bit, others would just do some disk i/o and make you feel good, but none of them are really needed anymore.

-PatP

Oracle statement

Hi all,
what do i need to do to use in Oracle query statement the parameter? In SQL
server, I use @.ParameterName but I can not use it like that in Oracle.
In SQL Server: select * from tableA where columnID = @.ParameterName
This works great in SQL
I can not use the same thing in oracle. Please help. ThanksTry Using just the question mark "?". That's what I have been doing. It
seems that the oracle client cannot handle named parameters.
"VNN" <VNN@.hotmail.com> wrote in message
news:%23ZvRckZDFHA.1280@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> what do i need to do to use in Oracle query statement the parameter? In
> SQL server, I use @.ParameterName but I can not use it like that in Oracle.
> In SQL Server: select * from tableA where columnID = @.ParameterName
> This works great in SQL
> I can not use the same thing in oracle. Please help. Thanks
>|||I use the question mark "?" but I got another error message:
ORA-01036: illegal variable name/number.
I put the where columnName='?' and run the query, it pops up a dialog to
type in the value (dropdown list has null and blank), this is the same
dialog when I use SQL with @.parameterName, I entered the string value and
hit OK, then I got the error dialog box.
Please advice.
"Gregory C. Quinn, Jr." <gquinn@.triad.rr.com> wrote in message
news:X12Od.68699$fE4.11961448@.twister.southeast.rr.com...
> Try Using just the question mark "?". That's what I have been doing. It
> seems that the oracle client cannot handle named parameters.
> "VNN" <VNN@.hotmail.com> wrote in message
> news:%23ZvRckZDFHA.1280@.TK2MSFTNGP10.phx.gbl...
> > Hi all,
> >
> > what do i need to do to use in Oracle query statement the parameter? In
> > SQL server, I use @.ParameterName but I can not use it like that in
Oracle.
> >
> > In SQL Server: select * from tableA where columnID = @.ParameterName
> >
> > This works great in SQL
> >
> > I can not use the same thing in oracle. Please help. Thanks
> >
>|||I think Oracle uses a : but I am not sure. I remember it is not a @. and it
is not a ?. Try
select * from tableA where columnID = :ParameterName
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"VNN" <VNN@.hotmail.com> wrote in message
news:u%23jQXLiDFHA.936@.TK2MSFTNGP12.phx.gbl...
> I use the question mark "?" but I got another error message:
> ORA-01036: illegal variable name/number.
> I put the where columnName='?' and run the query, it pops up a dialog to
> type in the value (dropdown list has null and blank), this is the same
> dialog when I use SQL with @.parameterName, I entered the string value and
> hit OK, then I got the error dialog box.
> Please advice.
> "Gregory C. Quinn, Jr." <gquinn@.triad.rr.com> wrote in message
> news:X12Od.68699$fE4.11961448@.twister.southeast.rr.com...
> > Try Using just the question mark "?". That's what I have been doing.
It
> > seems that the oracle client cannot handle named parameters.
> >
> > "VNN" <VNN@.hotmail.com> wrote in message
> > news:%23ZvRckZDFHA.1280@.TK2MSFTNGP10.phx.gbl...
> > > Hi all,
> > >
> > > what do i need to do to use in Oracle query statement the parameter?
In
> > > SQL server, I use @.ParameterName but I can not use it like that in
> Oracle.
> > >
> > > In SQL Server: select * from tableA where columnID = @.ParameterName
> > >
> > > This works great in SQL
> > >
> > > I can not use the same thing in oracle. Please help. Thanks
> > >
> >
> >
>|||This depends on what you are doing. Oracle is a bit special. If you use the
graphical query designer it is using OLEDB if you are using the generic it
is using the managed provider. At runtime it uses the managed provider. My
suggestion is to use the generic query designer. Then do this:
select * from sometable where somefield = :ParameterName
With the managed provider that is how a named parameter works.
Here is some additional info from a MS dude:
>>>>>>>>>>>>
Robert Bruckner [MSFT] Mar 15 2004, 8:03 pm show options
Newsgroups: microsoft.public.sqlserver.reportingsvcs
From: "Robert Bruckner [MSFT]" <rob...@.online.microsoft.com> - Find
messages by this author
Date: Mon, 15 Mar 2004 20:00:54 -0800
Local: Mon, Mar 15 2004 8:00 pm
Subject: Re: Dynamic Oracle Query with Parameters Example (not using
Packages/Procs)
Reply to Author | Forward | Print | Individual Message | Show original
| Report Abuse
A good KB article is given at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;834305
The Visual Data Tools (VDT) query designer (2 panes) actually uses OLE DB in
the preview pane. The text-based generic query designer (GQD; 4 panes) uses
the .NET provider for Oracle. Whenever you get strange results in VDT, you
should try it with GQD.
Note: the syntax for parameters in oracle queries depends on the provider
you are using:
* Managed Oracle provider (named parameters):
select from table where ename = :parameter
* OLE DB for Oracle (unnamed parameters):
select from table where ename = ?
The parameter binding between query parameters (accessible on the dataset
dialog) and report parameters (accessible through the menu - report - report
parameters) will be done automatically by the query designer.
Robert M. Bruckner
Microsoft SQL Server Reporting Services
>>>>>>>>>>>>>>>>>>
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"VNN" <VNN@.hotmail.com> wrote in message
news:u%23jQXLiDFHA.936@.TK2MSFTNGP12.phx.gbl...
> I use the question mark "?" but I got another error message:
> ORA-01036: illegal variable name/number.
> I put the where columnName='?' and run the query, it pops up a dialog to
> type in the value (dropdown list has null and blank), this is the same
> dialog when I use SQL with @.parameterName, I entered the string value and
> hit OK, then I got the error dialog box.
> Please advice.
> "Gregory C. Quinn, Jr." <gquinn@.triad.rr.com> wrote in message
> news:X12Od.68699$fE4.11961448@.twister.southeast.rr.com...
> > Try Using just the question mark "?". That's what I have been doing.
It
> > seems that the oracle client cannot handle named parameters.
> >
> > "VNN" <VNN@.hotmail.com> wrote in message
> > news:%23ZvRckZDFHA.1280@.TK2MSFTNGP10.phx.gbl...
> > > Hi all,
> > >
> > > what do i need to do to use in Oracle query statement the parameter?
In
> > > SQL server, I use @.ParameterName but I can not use it like that in
> Oracle.
> > >
> > > In SQL Server: select * from tableA where columnID = @.ParameterName
> > >
> > > This works great in SQL
> > >
> > > I can not use the same thing in oracle. Please help. Thanks
> > >
> >
> >
>|||That's it. It works great. Thanks very much.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:u7kH6diDFHA.3908@.TK2MSFTNGP15.phx.gbl...
> This depends on what you are doing. Oracle is a bit special. If you use
> the
> graphical query designer it is using OLEDB if you are using the generic it
> is using the managed provider. At runtime it uses the managed provider. My
> suggestion is to use the generic query designer. Then do this:
> select * from sometable where somefield = :ParameterName
> With the managed provider that is how a named parameter works.
> Here is some additional info from a MS dude:
>>>>>>>>>>>>
> Robert Bruckner [MSFT] Mar 15 2004, 8:03 pm show options
> Newsgroups: microsoft.public.sqlserver.reportingsvcs
> From: "Robert Bruckner [MSFT]" <rob...@.online.microsoft.com> - Find
> messages by this author
> Date: Mon, 15 Mar 2004 20:00:54 -0800
> Local: Mon, Mar 15 2004 8:00 pm
> Subject: Re: Dynamic Oracle Query with Parameters Example (not using
> Packages/Procs)
> Reply to Author | Forward | Print | Individual Message | Show
> original
> | Report Abuse
> A good KB article is given at:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;834305
>
> The Visual Data Tools (VDT) query designer (2 panes) actually uses OLE DB
> in
> the preview pane. The text-based generic query designer (GQD; 4 panes)
> uses
> the .NET provider for Oracle. Whenever you get strange results in VDT, you
> should try it with GQD.
>
> Note: the syntax for parameters in oracle queries depends on the provider
> you are using:
> * Managed Oracle provider (named parameters):
> select from table where ename = :parameter
> * OLE DB for Oracle (unnamed parameters):
> select from table where ename = ?
>
> The parameter binding between query parameters (accessible on the dataset
> dialog) and report parameters (accessible through the menu - report -
> report
> parameters) will be done automatically by the query designer.
>
> --
> Robert M. Bruckner
> Microsoft SQL Server Reporting Services
>>>>>>>>>>>>>>>>>>
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
>
>
> "VNN" <VNN@.hotmail.com> wrote in message
> news:u%23jQXLiDFHA.936@.TK2MSFTNGP12.phx.gbl...
>> I use the question mark "?" but I got another error message:
>> ORA-01036: illegal variable name/number.
>> I put the where columnName='?' and run the query, it pops up a dialog to
>> type in the value (dropdown list has null and blank), this is the same
>> dialog when I use SQL with @.parameterName, I entered the string value and
>> hit OK, then I got the error dialog box.
>> Please advice.
>> "Gregory C. Quinn, Jr." <gquinn@.triad.rr.com> wrote in message
>> news:X12Od.68699$fE4.11961448@.twister.southeast.rr.com...
>> > Try Using just the question mark "?". That's what I have been doing.
> It
>> > seems that the oracle client cannot handle named parameters.
>> >
>> > "VNN" <VNN@.hotmail.com> wrote in message
>> > news:%23ZvRckZDFHA.1280@.TK2MSFTNGP10.phx.gbl...
>> > > Hi all,
>> > >
>> > > what do i need to do to use in Oracle query statement the parameter?
> In
>> > > SQL server, I use @.ParameterName but I can not use it like that in
>> Oracle.
>> > >
>> > > In SQL Server: select * from tableA where columnID = @.ParameterName
>> > >
>> > > This works great in SQL
>> > >
>> > > I can not use the same thing in oracle. Please help. Thanks
>> > >
>> >
>> >
>>
>