Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Conditional Order By


Conditional Order By

Author
Message
DugyC
DugyC
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1324 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"
Richard Warr
Richard Warr
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2578 Visits: 1983
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!

_____________________________________________________________________
MCSA SQL Server 2012
ronmoses
ronmoses
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1095 Visits: 996
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.

:-P
ron

-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown

Mattrick
Mattrick
SSChasing Mays
SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)

Group: General Forum Members
Points: 602 Visits: 767
I got tripped up by the implicit conversion. That should teach me to answer these questions before properly caffeinated. Thanks for the question!
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16509 Visits: 13207
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
OCTom
OCTom
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2815 Visits: 4152
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. w00t

Tha could come in handy some day. Thanks.
Daniel Bowlin
Daniel Bowlin
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2954 Visits: 2629
I don't understand the implicit conversion. Why is it happening?
mister.magoo
mister.magoo
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2274 Visits: 7827
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


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • 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

  • mister.magoo
    mister.magoo
    SSCrazy
    SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

    Group: General Forum Members
    Points: 2274 Visits: 7827
    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


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • 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

  • Carlo Romagnano
    Carlo Romagnano
    Hall of Fame
    Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

    Group: General Forum Members
    Points: 3626 Visits: 3236
    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! :-)
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search