Can I ORDER BY an Alias Column (MSSQL)?

[WWGD]

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.

 

[Samy_A]

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.

You can use ORDER BY x.
The following query on the Northwinds database works:

SELECT CompanyName as expr2, ContactName, Left(Country,2) AS Expr1
FROM Customers
ORDER BY expr2,expr1;

 

[pbuk]

Still having trouble downloading free copy of MSSQL2012, so I cannot test the query.

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.

 

[WWGD]

You can use ORDER BY x.
The following query on the Northwinds database works:

SELECT CompanyName as expr2, ContactName, Left(Country,2) AS Expr1
FROM Customers
ORDER BY expr2,expr1;

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?

 

[Samy_A]

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?

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.