August 21, 2006 at 12:50 am
Or am I plain stupid?
The problem:
Create a table:
create table dbo.Numbers (
Number1 integer not null,
Number2 integer not null,
) go
Populate it:
INSERT INTO dbo.Numbers VALUES (2, 3)
INSERT INTO dbo.Numbers VALUES (4, 8)
INSERT INTO dbo.Numbers VALUES (7, 5)
go
This statement works:
DECLARE @OrderBy varchar(33)
SET @OrderBy = 'Number2'
SELECT * FROM (SELECT
N1 = A.Number1
, N2 = A.Number2
FROM dbo.Numbers A
) X ORDER BY CASE
WHEN @OrderBy = 'Number1' THEN CAST(N1 as varchar)
WHEN @OrderBy = 'Number2' THEN CAST(N2 as varchar)
ELSE 2 END
This doesnt:
DECLARE @OrderBy varchar(33)
SET @OrderBy = ''
SELECT * FROM (SELECT
N1 = A.Number1
, N2 = A.Number2
FROM dbo.Numbers A
) X ORDER BY CASE
WHEN @OrderBy = 'Number1' THEN N1
WHEN @OrderBy = 'Number2' THEN N2
ELSE 2 END
Anybody explain to me?
/m
August 21, 2006 at 8:48 am
I tried to simplify this and not use a physical table.
Can you explain what you desire from your ELSE statement in the ORDER BY clause? This looks to work and the second second simply orders by N1 as a default...
DECLARE @Numbers TABLE( Number1 integer NOT NULL,
Number2 integer NOT NULL)
INSERT INTO @Numbers VALUES (2, 3)
INSERT INTO @Numbers VALUES (4, 8)
INSERT INTO @Numbers VALUES (7, 5)
DECLARE @OrderBy varchar(33)
SET @OrderBy = 'Number2'
SELECT * FROM (SELECT Number1 AS N1, Number2 AS N2
FROM @Numbers A) X
ORDER BY CASE
WHEN @OrderBy = 'Number1' THEN CAST( N1 AS varchar)
WHEN @OrderBy = 'Number2' THEN CAST( N2 AS varchar)
ELSE 2
END
SET @OrderBy = ''
SELECT * FROM (SELECT Number1 AS N1, Number2 AS N2
FROM @Numbers A) X
ORDER BY CASE
WHEN @OrderBy = 'Number1' THEN N1
WHEN @OrderBy = 'Number2' THEN N2
ELSE 2
END
I wasn't born stupid - I had to study.
August 21, 2006 at 2:52 pm
It seems Order By Clause is ignoring the sort column position in the result set if used in conjunction with case statement
see this!!
DECLARE
@OrderBy varchar(33)
SET
@OrderBy = 'Number2'
SELECT
* FROM (SELECT Number1 AS N1, Number2 AS N2
FROM Numbers A) X
ORDER
BY CASE
WHEN @OrderBy = 'Number1' THEN CAST( N1 AS varchar)
WHEN @OrderBy = 'Number2' THEN CAST( N2 AS varchar)
ELSE 2
END
SET
@OrderBy = 'Number2'
SELECT
N1,N2 FROM (SELECT Number1 AS N1, Number2 AS N2
FROM Numbers) X
ORDER
BY CASE
WHEN @OrderBy = 'Number1' THEN CAST( N1 AS varchar)
WHEN @OrderBy = 'Number2' THEN 2
ELSE 2
END ASC
August 21, 2006 at 2:56 pm
It's not ignoring it. As in the example, (the ELSE 2), you are setting it to '2' and there is no column '2'. Hence, it is defaulting to the first column and sorting upon that...
I wasn't born stupid - I had to study.
August 21, 2006 at 3:10 pm
Here 2 is not a column name, it is the position in the result set on which the sort operation to be performed
The second select in above mail is equivalent to
SELECT
* FROM (SELECT Number1 AS N1, Number2 AS N2 FROM Numbers) X ORDER BY 2
(Case statement will return the value 2)
Any suggestions?
August 21, 2006 at 3:44 pm
You're right. I cannot even force it to work:
DECLARE @Numbers TABLE( Number1 integer NOT NULL,
Number2 integer NOT NULL)
INSERT INTO @Numbers VALUES (2, 3)
INSERT INTO @Numbers VALUES (4, 8)
INSERT INTO @Numbers VALUES (7, 5)
DECLARE @OrderBy varchar(33),
@SQL varchar(350)
SET @OrderBy = 'Number2'
SELECT * FROM (SELECT Number1 AS N1, Number2 AS N2
FROM @Numbers A) X
ORDER BY CASE
WHEN @OrderBy = 'Number1' THEN X.N1
WHEN @OrderBy = 'Number2' THEN X.N2
ELSE 2
END
SET @OrderBy = 'X'
SELECT * FROM (SELECT Number1 AS N1, Number2 AS N2
FROM @Numbers A) X
ORDER BY CASE
WHEN @OrderBy = 'Number1' THEN X.N1
WHEN @OrderBy = 'Number2' THEN X.N2
WHEN @OrderBy = 'X' THEN 2
ELSE X.N2
END
I wasn't born stupid - I had to study.
August 22, 2006 at 12:24 am
Tried it on SQL2000 and also SQL2005:
--doesnt work
DECLARE
@OrderBy integer
SET
@OrderBy = 3
SELECT
* FROM (SELECT
N1 = A.Number1
,
N2 = A.Number2
FROM
dbo.Numbers A
)
X ORDER BY CASE
WHEN
@OrderBy = 1 THEN X.N1
WHEN
@OrderBy = 2 THEN X.N2
ELSE
2 END
doesnt work on either of them.
/m
August 22, 2006 at 3:09 am
Found a workaround:
DECLARE @OrderBy varchar(33)
SET @OrderBy = 'Number3'
SELECT X1, X2 FROM
(SELECT N1 = A.Number1
, N2 = A.Number2
FROM dbo.Numbers A) X (X1,X2)
ORDER BY CASE
WHEN @OrderBy = 'Number1' THEN X1
WHEN @OrderBy = 'Number2' THEN X2
ELSE X2 END
Funny though that I get no warningmessages on this:
DECLARE @OrderBy varchar(33)
SET @OrderBy = 'Number3'
SELECT X1, X2 FROM
(SELECT N1 = A.Number1
, N2 = A.Number2
FROM dbo.Numbers A) X (X1,X2)
ORDER BY CASE
WHEN @OrderBy = 'Number1' THEN 1
WHEN @OrderBy = 'Number2' THEN 2
ELSE 2 END
/m
August 22, 2006 at 10:39 am
An order by expression can be a non-negative integer to represent an expression in the select list; however, I expect that an expression that evaluates to an integer is just an integer, not a representation of another expression. If this is really what you want, then an ad hoc query would be required.
I think IF ELSE would be best for performance if there are only two or three sort orders. Even if there are many possible sort orders, the IF ELSE can be used to hard codes the most popular sort orders. The last ELSE can contain the query to dynamically handle for the remaining sort orders - perhaps using case statements.
Randy
Helpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
August 22, 2006 at 10:45 am
michael.rosquist : The following is not giving the proper output ( ordered by 2nd column)
DECLARE @OrderBy varchar(33)
SET @OrderBy = 'Number3'
SELECT X1, X2 FROM
(SELECT N1 = A.Number1
, N2 = A.Number2
FROM dbo.Numbers A) X (X1,X2)
ORDER BY CASE
WHEN @OrderBy = 'Number1' THEN 1
WHEN @OrderBy = 'Number2' THEN 2
ELSE 2 END
Where is the workaround?
August 22, 2006 at 3:47 pm
michael.rosquit, I do get an error when running your second section of code:
Server: Msg 1008, Level 15, State 1, Line 29
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.
I also do not get the required results when using your work-around, (it still orders by the first column)
DECLARE @Numbers TABLE( Number1 integer NOT NULL,
Number2 integer NOT NULL)
INSERT INTO @Numbers VALUES (2, 3)
INSERT INTO @Numbers VALUES (4, 8)
INSERT INTO @Numbers VALUES (7, 5)
DECLARE @OrderBy varchar(33),
@SQL varchar(350)
SET @OrderBy = 'Number2'
SELECT X.N1, X.N2
FROM (SELECT Number1 AS N1, Number2 AS N2
FROM @Numbers A) X
ORDER BY CASE
WHEN @OrderBy = 'Number1' THEN X.N1
WHEN @OrderBy = 'Number2' THEN X.N2
ELSE 2
END
SET @OrderBy = 'Number3'
SELECT X1, X2
FROM (SELECT Number1 AS N1, Number2 AS N2
FROM @Numbers A) X (X1, X2)
ORDER BY CASE
WHEN @OrderBy = 'Number1' THEN X1
WHEN @OrderBy = 'Number2' THEN X2
ELSE 2
END
I wasn't born stupid - I had to study.
August 22, 2006 at 7:17 pm
I think the point is that you can't use a variable or expression to specify the column. You also can't use a variable to specify syntax or table names. You get to either use the 1, 2, 3 etc to represent the "column" in the select list or a CASE statment expression, not both. Any integer in the CASE will not represent a column, just the value of the integer.
ORDER BY 1, 2 -- reference items in the select list
ORDER BY CASE WHEN 1=1 THEN 1 ELSE 1 END, CASE WHEN 1=1 THEN 2 ELSE 2 END -- just values
Randy
Helpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
August 23, 2006 at 12:23 am
Workaround:
DECLARE
@OrderBy varchar(33)
SET
@OrderBy = 'Number3'
SELECT
X1, X2 FROM
(SELECT N1 = A.Number1, N2 = A.Number2
FROM dbo.Numbers A) X (X1,X2)
ORDER
BY CASE
WHEN
@OrderBy = 'Number1' THEN X1
WHEN
@OrderBy = 'Number2' THEN X2
ELSE
X2 END
==> order by X2
No errormessages, wrong answer:
DECLARE
@OrderBy varchar(33)
SET
@OrderBy = 'Number3'
SELECT
X1, X2 FROM
(SELECT N1 = A.Number1, N2 = A.Number2
FROM dbo.Numbers A) X (X1,X2)
ORDER
BY CASE
WHEN
@OrderBy = 'Number1' THEN X1
WHEN
@OrderBy = 'Number2' THEN X2
ELSE
2 END
==> no order by second column
This gives errormessage:
DECLARE
@OrderBy varchar(33)
SET
@OrderBy = 'Number3'
SELECT
X1, X2 FROM
(SELECT N1 = A.Number1, N2 = A.Number2
FROM dbo.Numbers A) X (X1,X2)
ORDER
BY CASE
WHEN
@OrderBy = 'Number1' THEN X1,X2
WHEN
@OrderBy = 'Number2' THEN X2,X1
ELSE
X2 END
==>
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ','.
All run in SQL 2005.
/m
August 30, 2006 at 10:18 am
In the last example, rather than trying to alter the ORDER BY syntax with one case statement, use a case statement for each element in the ORDER BY
ORDER BY
CASE @OrderBy WHEN 'Number1' THEN X1 WHEN 'Number2' THEN X2 ELSE X2 END,
CASE @OrderBy WHEN 'Number1' THEN X2 WHEN 'Number2' THEN X1 END -- ELSE NULL is implied
-- 'Number1' -> ORDER BY X1, X2
-- 'Number2' -> ORDER BY X2, X1
-- other -> ORDER BY X2, NULL
Or, use logical tests to write one query for each order by required.
IF @OrderBy = 'Number1' BEGIN
... ORDER BY X1, X2
END
ELSE IF @OrderBy = 'Number2' BEGIN
... ORDER BY X2, X1
END
ELSE BEGIN
... ORDER BY X2
END
Randy
Helpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
August 31, 2006 at 4:06 am
I finally arrived at this:
create table dbo.Numbers (
Number1 integer not null,
Number2 integer not null,
)
go
delete
from dbo.Numbers
INSERT
INTO dbo.Numbers VALUES (2, 9)
INSERT
INTO dbo.Numbers VALUES (2, 3)
INSERT
INTO dbo.Numbers VALUES (4, 8)
INSERT
INTO dbo.Numbers VALUES (7, 5)
INSERT
INTO dbo.Numbers VALUES (1, 2)
INSERT
INTO dbo.Numbers VALUES (1, 9)
go
DECLARE
@OrderBy varchar(33)
SET
@OrderBy = 'Number3'
SELECT
X1, X2 FROM
(SELECT N1 = A.Number1
, N2 = A.Number2
FROM dbo.Numbers A) X (X1,X2)
ORDER
BY CASE
WHEN
@OrderBy = 'Number1' THEN X1
WHEN
@OrderBy = 'Number2' THEN X2
ELSE
X2 END,
CASE
WHEN
@OrderBy = 'Number1' THEN X2
WHEN
@OrderBy = 'Number2' THEN X1
ELSE
X1 END
but this still doesnt work:
DECLARE
@OrderBy varchar(33)
SET
@OrderBy = 'Number3'
SELECT
X1, X2 FROM
(SELECT N1 = A.Number1
, N2 = A.Number2
FROM dbo.Numbers A) X (X1,X2)
ORDER
BY CASE
WHEN
@OrderBy = 'Number1' THEN 1
WHEN
@OrderBy = 'Number2' THEN 2
ELSE
2 END,
CASE
WHEN
@OrderBy = 'Number1' THEN 2
WHEN
@OrderBy = 'Number2' THEN 1
ELSE
1 END
and no errormessages neither in sql2000 nor in sql2005
Comments?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply