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%'

ee1

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

ee2In 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

ee3So in CASE expression even using in ORDER BY clause, You may not use alias of column.

Leave a Reply

Your email address will not be published.

Time limit is exhausted. Please reload CAPTCHA.