Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2008
    Location
    Yangon,Myanmar
    Posts
    10

    Talking Unanswered: How can i order define column?

    I use SQL server 2000
    In my query i define one type of column such as this example
    select Companyname+firstname+lastname as'name' from Suppliers
    I would like to order predefine column name not use Companyname


    SELECT col1
    , col2
    , company_name + firstname + lastname As [name]
    FROM _table
    (select * from My_tables )
    ORDER
    BY [name]

    I don't like to add predefine column in the sub query.
    only would like to add main query

    kongaung@gmail.com
    Last edited by kongaung; 12-30-08 at 05:55.

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Add "ORDER BY name" to your query.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I prefer the subquery approach
    Code:
    SELECT *
    FROM   (
            SELECT col1
                 , col2
                 , company_name + firstname + lastname As [name]
            FROM   your_table
           ) As [a_subquery]
    ORDER
        BY [name]
    George
    Home | Blog

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i prefer dportas' simpler approach
    Code:
    SELECT col1
         , col2
         , company_name + firstname + lastname As [name]
      FROM daTable 
    ORDER
        BY name
    alternatively, you could do this --
    Code:
    SELECT col1
         , col2
         , company_name + firstname + lastname As [name]
      FROM daTable 
    ORDER
        BY company_name, firstname, lastname
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I originally suggested the subquery as opposed to "ORDER BY [name]" because I could have sworn I'd come across isntances where this wouldn't work (unknown column for instance) but cannot reproduce the behavior now so must concede!

    And your second suggestion is peachy when we're dealing with simple expressions. If we had
    Code:
    SELECT col1
         , col2
         , company_name + Right(firstname, 1) + lastname As [name]
    FROM  your_table
    then I think you'd agree that we should use the anmed alias too
    George
    Home | Blog

  6. #6
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    George, you could be right about there being instances where it might not work. There were actually several bugs with ORDER BY and aliasing in some releases of SQL Server 2000.

    I didn't read the original question very well because the ORDER BY was already in the example query. If that doesn't give the expected result then maybe it's due to a bug. Kongaung should check what build he has installed and look at microsoft.com for any relevant fixes. Unfortunately I don't have access to any 2000 instance at the moment so I can't even test it out.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by georgev
    I originally suggested the subquery as opposed to "ORDER BY [name]" because I could have sworn I'd come across isntances where this wouldn't work (unknown column for instance) but cannot reproduce the behavior now so must concede!
    Ditto here. I don't know when they slipped that one past me, but I just tried it and it works on both 2005 and 2000.
    If it's not practically useful, then it's practically useless.

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

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Ah. I see the confusion now...
    ...the alias can be referenced in the ORDER BY clause, but cannot be referenced in a WHERE clause.
    If it's not practically useful, then it's practically useless.

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

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Aha! Well that does make sense if you think about it I suppose
    George
    Home | Blog

Posting Permissions

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