Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104

    Unanswered: ORDER BY using @variables

    Hi

    I realise you can't declare a column name as a @variable:

    SELECT name, address FROM table WHERE name = 'smith' ORDER BY @column

    .....but other than using dynamic SQL, is there a better way i can ORDER a query using various columns?

    thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    something like this perhaps?
    Code:
    order
        by case @flag
              when 1 then column1
              when 2 then column2
              when 3 then column3
              else null end
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Ordering logic belongs at the presentation layer, not the database layer.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    WHAT??!!!!

    please explain, oh database guru

    you're suggesting that we ditch the ORDER BY clause altogether?

    something about that idea just doesn't sit too well with me...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'm gonna report this thread
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    Thanks for that r937, works spot on!

    Just have another question, for some reason i get the following error when trying to add the case statement to a UNION query:

    ORDER BY items must appear in the select list if the statement contains a UNION operator.
    Now i know that you can define a single ORDER BY clause in a union query and that it should appear after the last SELECT which it is. I've tried manually putting the ORDER BY clause into the query and it works fine, but it just doesnt like the case statement for some reason? Is there a way round it?

    cheers

  7. #7
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Just note two conditions on performing UNIONS :-

    • The number and the order of the columns must be the same in all queries.
    • The data types must be compatible.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mattock
    Is there a way round it?
    there might be, but i can't really help because i can't see the query from here
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    sorry, should have put it in - this is a simlified version of my MSSQL query - it still brings up the same error:

    SELECT business_name, address1
    FROM VENUE
    WHERE (business_name LIKE '%' + @v_name + '%')
    UNION
    SELECT business_name, address1
    FROM AHOTELS
    WHERE business_name Like '%' + @v_name + '%'
    ORDER BY CASE @sortBy
    WHEN 1 THEN business_name
    WHEN 2 THEN postcode
    WHEN 3 THEN town
    ELSE NULL END

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by r937
    WHAT??!!!!

    please explain, oh database guru
    Ordering is normally a presentation issue. Databases should not be concerned with the manner in which data is presented.
    Quote Originally Posted by r937
    you're suggesting that we ditch the ORDER BY clause altogether?
    Of course not. Would you suggest that we ditch cursors just because they are often misused?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mattock
    it still brings up the same error:
    which is... ?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman
    Ordering is normally a presentation issue. Databases should not be concerned with the manner in which data is presented.
    next time i see you post a SELECT statement with an ORDER BY clause, you are gonna get hit with a big can of whoopass from me, then
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    This is the error:

    ORDER BY items must appear in the select list if the statement contains a UNION operator.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, that's pretty clear, isn't it

    SELECT business_name, address1, postcode, town
    FROM VENUE
    WHERE (business_name LIKE '%' + @v_name + '%')
    UNION
    SELECT business_name, address1, postcode, town
    FROM AHOTELS
    WHERE business_name Like '%' + @v_name + '%'
    ORDER BY CASE @sortBy
    WHEN 1 THEN business_name
    WHEN 2 THEN postcode
    WHEN 3 THEN town
    ELSE NULL END
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    but I'm sure they don't want it in the result set
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •