April 14, 2009 at 12:57 am
Hi
How would one programatically ORDER BY? This is what I am trying to do:
declare @a as varchar(1) = '1';
set @a = '2';
select * from b1
order by
CASE
WHEN @a = '1' THEN cast(b1 AS varchar(10))
when @a = '2' then b12
END
case
when 'b' = 'b' then desc
when 'b' = 'c' then asc
end
I get this error:
Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'case'.
This works fine, but doesn't quite accomplish what I am trying to do:
declare @a as varchar(1) = '1';
set @a = '2';
select * from b1
order by
CASE
WHEN @a = '1' THEN cast(b1 AS varchar(10))
when @a = '2' then b12
END
desc -- or just left out if the data needs to be sorted asc.
I've also tried select * from b1
order by
CASE
WHEN @a = '1' THEN cast(b1 AS varchar(10)) desc
when @a = '2' then b12 desc
END
--case
--when 'b' = 'b' then desc
--when 'b' = 'c' then asc
--end with this error:
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'desc'.
I've searched around a bit to get this far, but it seems that the answer to this asc/desc question may lie here with you guys 😉
Side question: Why do I need to CAST the integer b1 as a varchar(10)?
Thank you very much!
_________________________________________________________
Creation script:
CREATE TABLE [dbo].[b1](
[b1] [int] NOT NULL,
[b12] [varchar](10) NOT NULL
) ON [PRIMARY]
INSERT [dbo].[b1] ([b1], [b12]) VALUES (1, N'bob11')
INSERT [dbo].[b1] ([b1], [b12]) VALUES (2, N'bob12')
INSERT [dbo].[b1] ([b1], [b12]) VALUES (3, N'bob13')
INSERT [dbo].[b1] ([b1], [b12]) VALUES (4, N'bob14')
INSERT [dbo].[b1] ([b1], [b12]) VALUES (5, N'bob15')
INSERT [dbo].[b1] ([b1], [b12]) VALUES (6, N'bob16')
INSERT [dbo].[b1] ([b1], [b12]) VALUES (7, N'bob17')
April 21, 2009 at 3:23 pm
I changed your creation script so you can see that the script here is working correctly. Give it a shot, this should work for you.
The main problem you are having is because you can't put the ASC/DESC inside the case statement which is normal. With the sort order outside the case statement it works fine, but you need to use two separate case statements in the order by clause to do this. Essentially the @a is the column number and @b-2 is the order. The nice thing here as well is that you would be able to sort by additional columns if you chose to.
declare @a as varchar(1)
select @a = '2' --column number in select statement to order by
declare @b-2 as varchar(1)
select @b-2 = 'a' --sort order 'a' or 'd' for ascending and descending respectively
SELECT b1, b12
FROM b1
ORDER BY
CASE
WHEN @a='1' AND @b-2='a' THEN CAST(b1 AS varchar(10))
WHEN @a='2' AND @b-2='a' THEN b12
ELSE NULL
END ASC,
CASE
WHEN @a='1' AND @b-2='d' THEN CAST(b1 AS varchar(10))
WHEN @a='2' AND @b-2='d' THEN b12
ELSE NULL
END DESC
Creation script:
CREATE TABLE [dbo].[b1](
[b1] [int] NOT NULL,
[b12] [varchar](10) NOT NULL
) ON [PRIMARY]
INSERT [dbo].[b1] ([b1], [b12]) VALUES (1, N'bob17')
INSERT [dbo].[b1] ([b1], [b12]) VALUES (2, N'bob16')
INSERT [dbo].[b1] ([b1], [b12]) VALUES (3, N'bob15')
INSERT [dbo].[b1] ([b1], [b12]) VALUES (4, N'bob14')
INSERT [dbo].[b1] ([b1], [b12]) VALUES (5, N'bob13')
INSERT [dbo].[b1] ([b1], [b12]) VALUES (6, N'bob12')
INSERT [dbo].[b1] ([b1], [b12]) VALUES (7, N'bob11')
August 20, 2013 at 5:55 pm
Hi AndrewJacksonZA,
Did this work? If you remember 😉
August 22, 2013 at 8:22 pm
How about something like this?
CREATE TABLE #b1(
[b1] [int] NOT NULL,
[b12] [varchar](10) NOT NULL
) ON [PRIMARY]
INSERT #b1 ([b1], [b12]) VALUES (1, N'bob11')
INSERT #b1 ([b1], [b12]) VALUES (2, N'bob12')
INSERT #b1 ([b1], [b12]) VALUES (3, N'bob13')
INSERT #b1 ([b1], [b12]) VALUES (4, N'bob14')
INSERT #b1 ([b1], [b12]) VALUES (5, N'bob15')
INSERT #b1 ([b1], [b12]) VALUES (6, N'bob16')
INSERT #b1 ([b1], [b12]) VALUES (7, N'bob17')
DECLARE @sort INT = 1; -- DESC
WITH MyTable AS (
SELECT b1, b12
,a=ROW_NUMBER() OVER (ORDER BY b12)
FROM #b1)
SELECT b1, b12
FROM MyTable
ORDER BY a*POWER(-1, @sort);
SET @sort = 0; -- ASC
WITH MyTable AS (
SELECT b1, b12
,a=ROW_NUMBER() OVER (ORDER BY b12)
FROM #b1)
SELECT b1, b12
FROM MyTable
ORDER BY a*POWER(-1, @sort);
GO
DROP TABLE #b1;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply