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 Monday, November 15, 2010 9:30 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 5:29 PM
Points: 1,787, Visits: 5,693
Comments posted to this topic are about the item Conditional Order By

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 #1021197
    Posted Monday, November 15, 2010 11:12 PM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Monday, July 21, 2014 3:43 AM
    Points: 1,938, Visits: 1,162
    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
    Posted Monday, November 15, 2010 11:53 PM
    UDP Broadcaster

    UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

    Group: General Forum Members
    Last Login: Sunday, June 29, 2014 11:26 PM
    Points: 1,481, Visits: 1,960
    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
    Posted Tuesday, November 16, 2010 1:07 AM


    SSCrazy

    SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

    Group: General Forum Members
    Last Login: Yesterday @ 1:25 AM
    Points: 2,495, Visits: 2,376
    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
    Posted Tuesday, November 16, 2010 1:20 AM
    UDP Broadcaster

    UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

    Group: General Forum Members
    Last Login: Sunday, June 29, 2014 11:26 PM
    Points: 1,481, Visits: 1,960
    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
    Posted Tuesday, November 16, 2010 1:25 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, January 2, 2014 9:57 AM
    Points: 554, Visits: 863

    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
    Posted Tuesday, November 16, 2010 1:30 AM


    SSCrazy

    SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

    Group: General Forum Members
    Last Login: Yesterday @ 1:25 AM
    Points: 2,495, Visits: 2,376
    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
    Posted Tuesday, November 16, 2010 2:23 AM


    Ten Centuries

    Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

    Group: General Forum Members
    Last Login: Monday, August 18, 2014 6:23 AM
    Points: 1,411, Visits: 1,821
    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: @sqltwins
    Google Plus: +Nakul
    Post #1021304
    Posted Tuesday, November 16, 2010 2:39 AM


    SSCrazy

    SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

    Group: General Forum Members
    Last Login: Friday, August 15, 2014 4:01 AM
    Points: 2,108, Visits: 1,706
    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
    Posted Tuesday, November 16, 2010 2:49 AM
    UDP Broadcaster

    UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

    Group: General Forum Members
    Last Login: Sunday, June 29, 2014 11:26 PM
    Points: 1,481, Visits: 1,960
    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 »

    Add to briefcase 12345»»»

    Permissions Expand / Collapse