Conditional Order By

  • mister.magoo

    SSC-Forever

    Points: 47068

    Comments posted to this topic are about the item Conditional Order By

    MM



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

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

  • malleswarareddy_m

    SSCertifiable

    Points: 5847

    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)

  • tommyh

    SSCertifiable

    Points: 6252

    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

  • Carlo Romagnano

    SSC-Insane

    Points: 21948

    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.

  • tommyh

    SSCertifiable

    Points: 6252

    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

  • deepak.a

    SSCertifiable

    Points: 5330

    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

  • Carlo Romagnano

    SSC-Insane

    Points: 21948

    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'".:-D:-D

  • Nakul Vachhrajani

    SSChampion

    Points: 10210

    Excellent question!

    Thank-you!

    Thanks & Regards,
    Nakul Vachhrajani.
    http://nakulvachhrajani.com
    Be courteous. Drive responsibly.

    Follow me on
    Twitter: @sqltwins

  • Richard Warr

    SSCertifiable

    Points: 6957

    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.

    _____________________________________________________________________
    MCSA SQL Server 2012

  • tommyh

    SSCertifiable

    Points: 6252

    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)

  • DugyC

    Hall of Fame

    Points: 3804

    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.

    _____________________________________________________________________
    [font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]

  • Richard Warr

    SSCertifiable

    Points: 6957

    tommyh (11/16/2010)


    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@gmail.com

    SSCarpal Tunnel

    Points: 4480

    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

  • Mattrick

    Ten Centuries

    Points: 1176

    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

    SSC Guru

    Points: 258965

    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

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Viewing 15 posts - 1 through 15 (of 44 total)

    You must be logged in to reply to this topic. Login to reply