Tuesday, August 20, 2013

[SQL Server] Programatically ORDERing BY

[SQL Server] Programatically ORDERing BY

Programatically ORDERing BY

Posted: 13 Apr 2009 05:57 PM PDT

HiHow would one programatically ORDER BY? This is what I am trying to do:[code]declare @a as varchar(1) = '1';set @a = '2';select * from b1order byCASE WHEN @a = '1' THEN cast(b1 AS varchar(10)) when @a = '2' then b12 END casewhen 'b' = 'b' then descwhen 'b' = 'c' then ascend[/code]I get this error:[color=#ff0000]Msg 156, Level 15, State 1, Line 11Incorrect syntax near the keyword 'case'.[/color]This works fine, but doesn't [i]quite[/i] accomplish what I am trying to do:[code]declare @a as varchar(1) = '1';set @a = '2'; select * from b1order byCASE WHEN @a = '1' THEN cast(b1 AS varchar(10)) when @a = '2' then b12ENDdesc -- or just left out if the data needs to be sorted asc.[/code]I've also tried [code]select * from b1order byCASE WHEN @a = '1' THEN cast(b1 AS varchar(10)) desc when @a = '2' then b12 descEND--case--when 'b' = 'b' then desc--when 'b' = 'c' then asc--end[/code] with this error:[color=#ff0000]Msg 156, Level 15, State 1, Line 7Incorrect syntax near the keyword 'desc'.[/color]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!_________________________________________________________[b]Creation script:[/b][code]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')[/code]

Remove Leading Zero(s) only if needed

Posted: 27 Feb 2013 09:04 AM PST

I have four tables that have a varchar data type which gets imports or daily user updates and this field is an account number field which always has a length of 10, including leading zeros. My current code uses:UPDATE table.StatementsSET field03 = REPLACE(LTRIM(REPLACE(field03, '0', ' ')), ' ', '0')The issue is that this runs against all four tables and takes around 5 minutes. After the code runs, I see that it touched every record, which I don't want. I just want the code to look at the rows that this field needs to be changed on and disregard the previously trimmed rows.I know I need some kind of IF statement to accomplish this, but not sure how.TIA, Scott

last job that ran

Posted: 14 Nov 2012 02:51 AM PST

Hi all,If I have a job that executed twice, how can I, using t-sql, find which one executed last?Thanks,Eugene

Best way to store images

Posted: 08 Mar 2013 06:59 PM PST

Hi.I recognized if we store an image in binary type in SQL SERVER they take more space. For example a 700kb image take about 900kb space.With this condition which way is better to store images?

Shortcut for Views.

Posted: 20 Aug 2013 03:51 AM PDT

Is there a way to have short cut for Views. I do see for stored procedures... But not views.If not SSMS tools , is there a 3rd party tool I can install.

sql help, please

Posted: 20 Aug 2013 04:33 AM PDT

Hi all,Could someone help me make it work, please?I have the following case:[code="sql"]create table t (col1 varchar(1), col2 varchar(10), col3 int);insert into t values ('a','1a',1); insert into t values ('a','2a',1);[/code]select * from t;returns:[code="other"]a 1a 1a 2a 1[/code]I need it to be:[code="other"]a 1a,2a 1[/code]Thanks,
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog