<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by mister.magoo
»
Conditional Order By
44 posts, Page 1 of 5
1
2
3
4
5
»
»»
Conditional Order By
Rate Topic
Display Mode
Topic Options
Author
Message
mister.magoo
mister.magoo
Posted Monday, November 15, 2010 9:30 PM
Ten Centuries
Group: General Forum Members
Last Login: Yesterday @ 5:50 PM
Points: 1,308,
Visits: 3,899
Comments posted to this topic are about the item
Conditional Order By
MM
Post #1021197
malleswarareddy_m
malleswarareddy_m
Posted Monday, November 15, 2010 11:12 PM
SSCommitted
Group: General Forum Members
Last Login: Yesterday @ 9:44 PM
Points: 1,852,
Visits: 987
Non Sense question.I thought correct answer. means it will through error.but due to hint given by him i did not consider date and got wrong. am not happy with question and answer.
Malleswarareddy
I.T.Analyst
MCITP(70-451)
Post #1021223
tommyh
tommyh
Posted Monday, November 15, 2010 11:53 PM
UDP Broadcaster
Group: General Forum Members
Last Login: Friday, May 10, 2013 3:15 AM
Points: 1,476,
Visits: 1,943
Okay not the best question. However i wouldnt go as far to say it was nonsence. It shows that you cant mix different datatypes in a conditional order by without thinking about conversions.
I do however dislike the
WHEN @sortby = 'String1' THEN String2
WHEN @sortby = 'String2' THEN String1
part.
Sorting by 'String1' and then using column String2 (and vice versa) doesnt really add anything. It makes you think that its a trick question. So its easy to focus on that part instead.
So next time, no "trick" code to obscure what your trying to show ppl. Its really not needed.
/T
Post #1021242
Carlo Romagnano
Carlo Romagnano
Posted Tuesday, November 16, 2010 1:07 AM
SSCommitted
Group: General Forum Members
Last Login: Yesterday @ 12:56 AM
Points: 1,972,
Visits: 1,822
I should remember implicit cast using CASE clause.
Good question. I refreshed implicit cast.
In sql2000, it gives error also with SET @sortby='Date1'. (Syntax error converting datetime from character string.), not in sql2005.
Post #1021265
tommyh
tommyh
Posted Tuesday, November 16, 2010 1:20 AM
UDP Broadcaster
Group: General Forum Members
Last Login: Friday, May 10, 2013 3:15 AM
Points: 1,476,
Visits: 1,943
Carlo Romagnano (11/16/2010)
I should remember implicit cast using CASE clause.
Good question. I refreshed implicit cast.
In sql2000, it gives error also with SET @sortby='Date1'. (Syntax error converting datetime from character string.), not in sql2005.
No it doesnt
Well its not related to it being SQL2000. Probably some language issue. Works fine on mine.
/T
Post #1021266
deepak.a
deepak.a
Posted Tuesday, November 16, 2010 1:25 AM
Mr or Mrs. 500
Group: General Forum Members
Last Login: Monday, August 13, 2012 10:04 AM
Points: 554,
Visits: 861
this is the one of the way to handle those error.
DECLARE @sortby VARCHAR(10)
SET @sortby='String2'
SELECT String1, String2, Date1
FROM (
SELECT 'StringValue1', '2', CONVERT(datetime,'1 July 2010 00:01:00')
UNION ALL
SELECT 'StringValue2', '1', CONVERT(datetime,'2 July 2010 00:02:00')
) AS a(String1,String2,Date1)
ORDER BY
CASE
WHEN @sortby = 'String1' THEN String2
WHEN @sortby = 'String2' THEN String1
end,
CASE
WHEN @sortby = 'Date1' THEN Date1
END
Thanks & Regards
Deepak.A
Post #1021272
Carlo Romagnano
Carlo Romagnano
Posted Tuesday, November 16, 2010 1:30 AM
SSCommitted
Group: General Forum Members
Last Login: Yesterday @ 12:56 AM
Points: 1,972,
Visits: 1,822
tommyh (11/16/2010)
Carlo Romagnano (11/16/2010)
I should remember implicit cast using CASE clause.
Good question. I refreshed implicit cast.
In sql2000, it gives error also with SET @sortby='Date1'. (Syntax error converting datetime from character string.), not in sql2005.
No it doesnt
Well its not related to it being SQL2000. Probably some language issue. Works fine on mine.
/T
Also, I should remember "set language 'us_english'".
Post #1021276
Nakul Vachhrajani
Nakul Vachhrajani
Posted Tuesday, November 16, 2010 2:23 AM
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 1:02 AM
Points: 1,277,
Visits: 1,609
Excellent question!
Thank-you!
Thanks & Regards,
Nakul Vachhrajani.
http://beyondrelational.com/modules/2/blogs/77/nakuls-blog.aspx
Be courteous. Drive responsibly.
Follow me on
Twitter:
@nakulv_sql
Google Plus:
+Nakul
Post #1021304
Richard Warr
Richard Warr
Posted Tuesday, November 16, 2010 2:39 AM
SSCommitted
Group: General Forum Members
Last Login: Yesterday @ 8:39 AM
Points: 1,609,
Visits: 1,103
I thought it was an interesting question as well - currently more people have it wrong than right which shows that we can still be surprised at the "behind the scenes" antics of SQL Server.
Please don't describe questions as "nonsense", people take taime to set them and always have good intentions when they do so. It doesn't help your case if you can't spell "nonsense" either.
Post #1021317
tommyh
tommyh
Posted Tuesday, November 16, 2010 2:49 AM
UDP Broadcaster
Group: General Forum Members
Last Login: Friday, May 10, 2013 3:15 AM
Points: 1,476,
Visits: 1,943
Richard Warr (11/16/2010)
I thought it was an interesting question as well - currently more people have it wrong than right which shows that we can still be surprised at the "behind the scenes" antics of SQL Server.
Please don't describe questions as "nonsense", people take taime to set them and always have good intentions when they do so. It doesn't help your case if you can't spell "nonsense" either.
I know this wasnt directed at me (i dont think so anyway)... however.
You shouldnt complain about spelling. This is an international site and as long as you can understand what someone else writes... its okay. Perfect spelling/grammar is rare.
Also considering your own "taime" spelling... well... mistakes happen
/T (non native english speaker)
Post #1021319
« Prev Topic
|
Next Topic »
44 posts, Page 1 of 5
1
2
3
4
5
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
