Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Conditional Order By Expand / Collapse
Author
Message
Posted Tuesday, November 16, 2010 4:45 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 27, 2013 4:28 AM
Points: 1,248, Visits: 779
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.


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.


_____________________________________________________________________
"The difficult tasks we do immediately, the impossible takes a little longer"
Post #1021371
Posted Tuesday, November 16, 2010 5:15 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:57 AM
Points: 2,089, Visits: 1,692
tommyh (11/16/2010)
[quote]Richard Warr (11/16/2010)

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)


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!
Post #1021387
Posted Tuesday, November 16, 2010 5:49 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 25, 2014 6:26 AM
Points: 846, Visits: 850
tommyh (11/16/2010)
Richard Warr (11/16/2010)
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.

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

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


-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown
Post #1021409
Posted Tuesday, November 16, 2010 6:15 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, July 24, 2014 11:34 AM
Points: 581, Visits: 728
I got tripped up by the implicit conversion. That should teach me to answer these questions before properly caffeinated. Thanks for the question!
Post #1021420
Posted Tuesday, November 16, 2010 6:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:53 AM
Points: 13,310, Visits: 10,176
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




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1021462
Posted Tuesday, November 16, 2010 6:53 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 11:24 AM
Points: 2,517, Visits: 3,715
I got the question correct but not because I knew what I was doing. I haven't written T-SQL for very long and don't write complex code. I looked at the CASE statements in the ORDER BY and didn't know you could do that. It looked funny, so, I picked none of the above.

Tha could come in handy some day. Thanks.
Post #1021472
Posted Tuesday, November 16, 2010 7:05 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 24, 2014 8:41 AM
Points: 2,818, Visits: 2,553
I don't understand the implicit conversion. Why is it happening?
Post #1021485
Posted Tuesday, November 16, 2010 7:18 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 11:07 AM
Points: 1,783, Visits: 5,674
Daniel Bowlin (11/16/2010)
I don't understand the implicit conversion. Why is it happening?


Hi Daniel, good question!
Microsoft http://msdn.microsoft.com/en-us/library/ms190309.aspx

When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence. If the conversion is not a supported implicit conversion, an error is returned. When both operand expressions have the same data type, the result of the operation has that data type.

SQL Server uses the following precedence order for data types:

user-defined data types (highest)

sql_variant

xml

datetimeoffset

datetime2

datetime

smalldatetime

date

time

float

real

decimal

money

smallmoney

bigint

int

smallint

tinyint

bit

ntext

text

image

timestamp

uniqueidentifier

nvarchar (including nvarchar(max) )

nchar

varchar (including varchar(max) )

char

varbinary (including varbinary(max) )

binary (lowest)



MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1021495
    Posted Tuesday, November 16, 2010 7:22 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 11:07 AM
    Points: 1,783, Visits: 5,674
    Thanks to those of you who appreciate this question.

    To those who think it had unnecessary "tricks" in it, I did try several variants of this question and they all ended up being too easy.

    The "tricks" were deliberate and designed to check whether you would notice the implicit conversion and whether you even knew it would happen.

    If you don't like the question, I am sorry - for those that like to be challenged to check every small detail I think it provides a sufficient level of complexity to give you a good challenge!

    Have a nice day!


    MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1021501
    Posted Tuesday, November 16, 2010 7:40 AM


    SSCrazy

    SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

    Group: General Forum Members
    Last Login: Monday, July 21, 2014 6:24 AM
    Points: 2,451, Visits: 2,342
    mister.magoo (11/16/2010)
    Thanks to those of you who appreciate this question.

    To those who think it had unnecessary "tricks" in it, I did try several variants of this question and they all ended up being too easy.

    The "tricks" were deliberate and designed to check whether you would notice the implicit conversion and whether you even knew it would happen.

    If you don't like the question, I am sorry - for those that like to be challenged to check every small detail I think it provides a sufficient level of complexity to give you a good challenge!

    Have a nice day!

    I agree! Tricks train the eyes to see bugs quickly!
    Post #1021521
    « Prev Topic | Next Topic »

    Add to briefcase ««12345»»»

    Permissions Expand / Collapse