Conditional Order By

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.

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

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

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

Also, I should remember "set language 'us_english'".:-D:-D

Excellent question!

Thank-you!

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)

More people may have it wrong than right but its got less to do with the "behind the scenes" antics of SQL Server and more to do with the traps deliberately put into the question.

I've seen this problem before, and resolved it by seperating the CASE statement as suggested previously... however the traps set in the question tripped me up in this case.

tommyh (11/16/2010)

Fair point - nobody's perfect! 😉

Perhaps that distracted from what I was saying about having respect for all users and contributors though. And ever since Microsoft designated "British English" a foreign language we're in the same boat!

You shouldn't complain about people who complain about spelling on the part of people who complain about describing questions as "nonsense." Too many nested complaints will make your thread difficult to read and maintain. Instead, those complaints should be broken out into their own threads and called from the main thread.

😛

ron

I got tripped up by the implicit conversion. That should teach me to answer these questions before properly caffeinated. Thanks for the question!

I found this quite an excellent question. Sure, there was some trickery involved by switching string1 and string2 and by showing an example that works, but hey, you can't make it too easy 🙂

For more information about data type precedence, check the following url:

http://msdn.microsoft.com/en-us/library/ms190309.aspx