ORDER BY clause – when may use and when don’t may use alias of column in SQL Server 2012
It is two situation in SQL Server 2012 when You may or don’t use alias of column in ORDER BY
clause .
First example presents data from AdventureWorks2012
database from Person
table.
We get data of people: their lastname begin with Va
letters.
USE AdventureWorks2012; GO SELECT [Title], [FirstName], [LastName] ,YEAR([ModifiedDate]) as year FROM [AdventureWorks2012].[Person].[Person] WHERE [Title] is NOT NULL AND [LastName] LIKE 'Va%'
This data are not sorted. So use ORDER BY
clause.
USE AdventureWorks2012; GO SELECT [Title], [FirstName], [LastName] ,YEAR([ModifiedDate]) as year FROM [AdventureWorks2012].[Person].[Person] WHERE [Title] is NOT NULL AND [LastName] LIKE 'Va%' ORDER BY year
In simple ORDER BY
clause You may use alias of column. In this example this alias is year
.
In another example we use CASE
expression in ORDER BY
clause using alias of column.
USE AdventureWorks2012; GO SELECT [Title], [FirstName], [LastName] ,YEAR([ModifiedDate]) as year FROM [AdventureWorks2012].[Person].[Person] WHERE [Title] is NOT NULL AND [LastName] LIKE 'Va%' ORDER BY CASE year WHEN 2005 THEN FirstName ELSE LastName END
After execute this query, we get error message:
Msg 207, Level 16, State 1, Line 6 Invalid column name 'year'.
This will be work correct, if we use all expression instead of alias.
USE AdventureWorks2012; GO SELECT [Title], [FirstName], [LastName] ,YEAR([ModifiedDate]) as year FROM [AdventureWorks2012].[Person].[Person] WHERE [Title] is NOT NULL AND [LastName] LIKE 'Va%' ORDER BY CASE YEAR([ModifiedDate]) WHEN 2005 THEN FirstName ELSE LastName END
So in CASE
expression even using in ORDER BY
clause, You may not use alias of column.