Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Can I ORDER BY an Alias Column (MSSQL)?

Tags:
  1. Feb 9, 2016 #1

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    Hi All,
    I am doing a query in which I am ALIASING a column name, say 'column name' AS x
    Can I , at the end of the query ORDER BY x, or must I ORDER BY the given initial 'column name'?
    (Still having trouble downloading free copy of MSSQL2012, so I cannot test the query).
    I know this has to see with when the Alias is 'computed' in the query, but I am having trouble still.

    Thanks.
     
  2. jcsd
  3. Feb 9, 2016 #2

    Samy_A

    User Avatar
    Science Advisor
    Homework Helper

    You can use ORDER BY x.
    The following query on the Northwinds database works:
    Code (SQL):
    SELECT CompanyName AS expr2, ContactName, LEFT(Country,2) AS Expr1
    FROM Customers
    ORDER BY expr2,expr1;
     
  4. Feb 10, 2016 #3
    Can't remember if it was you or someone else, but possible solutions/work-arounds include (in increasing order of certainty):
    1. SQL Server 2014
    2. A cheap web host that runs SQL Server and run SQL Server Management Studio locally (although if you can't get SQL Server to work itself you might have problems)
    3. A cheap web host that runs SQL Server and provides an on-line management tool to run in your browser - this is guaranteed to work
    4. Install XAMPP and use MySql not MS SQL of course - fine for standard dialect (although watch out for CONCAT, that's usually the first thing to catch you out)
    5. Use an even cheaper web host to run MySQL (they all do this in their standard package) and provides an on-line management tool (usually PhpMyAdmin) to run in your browser - this is guaranteed to work
    6. Use sqlfiddle e.g. http://sqlfiddle.com/#!6/f892f/8 - this is also guaranteed to work and once you have set a reusable test schema is quicker to try for real than asking a question here (not that it is not a pleasure to answer your questions here of course...)
    A couple of solutions there that are guaranteed to work with various degrees of compromise, but anything is better than just guessing whether your code will work when you are learning.
     
  5. Feb 12, 2016 #4

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    Thanks, Samy_A . I guess this has to see with the order in which using ALIAS for a table is computed, i.e., it is computed after running the query, while , e.g., order by is computed/processed after the query is run, after the columns have already been aliased/renamed, right?
     
  6. Feb 12, 2016 #5

    Samy_A

    User Avatar
    Science Advisor
    Homework Helper

    I don't know much about the internal working of these database engines. But yes, it looks like that, or at least the database engine has a map of the fields and the aliases ready before running the query and doing the ordering.
    I have had rare instances with queries where sorting on the alias didn't work. As it was easily solved by just ordering on the expression itself, I never bothered to find out under what precise condition it doesn't work.
     
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook




Similar Discussions: Can I ORDER BY an Alias Column (MSSQL)?
  1. How can I code this? (Replies: 1)

  2. How i can learn ? (Replies: 16)

  3. Using c++ can I (Replies: 10)

Loading...