order by using IF

  • Sorry for coming up with the same issue.

    When I use Select distinct, then I am unable to use the order by with the other columns which are not pulled by Select distinct and also I am unable to select multiple columns, when I use the CASE statement

    when I say

    ORDER BY

    CASE when @sort = 1 then col1, col2............then an error is coming up.

     

     

  • Case cannot return 2 different values. You must use this technic :

    Order by

    Case when @sort = 'Col1' Then Col1 ELSE NULL end

    , Case when @sort = 'Col2' Then Col2 ELSE NULL end

    As for the distinct problem, this is ansi standard and I can't change that .

  • Surya - looks like the only thing you can do is build separate Selects based on @sort value...

    eg:

    IF @sort = 1

    BEGIN

    Select Col1, Col2, Col3

    From Table

    Order by Col1, Col2

    END

    **********

    IF @sort = 2

    BEGIN

    Select Col1, Col2, Col3

    From Table

    Order by Col2, Col1

    END

    As for the DISTINCT problem, since Remi cannot change the ansi standard (& I really thought he could do EVERYTHING...<;-)) - you have to come up with something else..like select columns that you don't need but not use it on the client side ?!?!?!







    **ASCII stupid question, get a stupid ANSI !!!**

  • Or actually do the right thing and write a few stored procs if you different sets of columns.

    Or the best of all, sort client side where it takes a huge load off the server and adds a little one on the client.

  • Sushila, so how's that coin tossing coming along?

  • I'm working on it Remi (Sheesh - what a slave driver!!!)

    I told you that it's really tough for me to make it heads or tails....







    **ASCII stupid question, get a stupid ANSI !!!**

  • And I thaught that DBAs could do anything. Another dream down the drain .

  • Hey - you started it first (shattering the dream ie) - you said that you couldn't change the ansi standard...and compared to you I'm waaaaaaaaaaaaaay down on the DBA ladder, so don't you go blaming me for anything!!!!!!!!

    p.s: Update on last throw 1 second back - it was still standing up!!!







    **ASCII stupid question, get a stupid ANSI !!!**

  • Sorry to shatter your dreams but I'm only a programmer

    BTW if the coin is actually rotating (flipping), it's almost impossible for it to stay there on its side without falling. Unless you're throwing it againsts a wall which would be illegal in this case.

  • I assure you that none of my actions are illegal - however, I cannot (& will not) reveal classified information....suffice it to say that I'm consistently getting "standing up" results AND playing by the rules!!

    Are you really a programmer and not a DBA Remi (or are you being funny again) ?!?!







    **ASCII stupid question, get a stupid ANSI !!!**

  • No I'm a programmer. I wish I could become a DBA someday.

    About the stand-up results, we'll need a video to proove that .

  • That is totally amazing - 'cos I've seen your responses in almost every single forum - ranging from "Administration" to "T-sql", "Access" to "Backups"...quite impressive! I don't think your DBA days are too far away!!!

    p.s: I'll be mailing the video as soon as I try a few more throws - I have at least 50 recorded already!







    **ASCII stupid question, get a stupid ANSI !!!**

  • Thanx... I'm trying to learn .

    Oh and I thaught you were talking about guys standing up when they saw you.

    My mitake .

  • I can't just use the IF because those columns are retrieved from so many tables, by so many joins, where clauses. It is abt 2 pages procedure and I cant just copy it and paste it all the times but I found it in a different way by using the temp tables.

    anyway thanks a lot. you and remi made this thread so enjoyable.

  • NP... can't wait to hear the answer of my last message .

Viewing 15 posts - 16 through 30 (of 32 total)

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