Saturday 19 January 2008

SQL 2005 : Column Alias in ORDER BY Clause

Found this today when migrating some views from 2000 to 2005.
You cannot use a column alias in ORDER BY Clause in SQL 2005.
The following works in sql 2000, but not sql 2005 -
SELECT LastName + ', ' + FirstName, TotalPurchaseYTD as Spend
FROM SalesIndividualCustomer Customer
INNER JOIN SalesIndividualDemographics Demographics
ON Customer.CustomerID = Demographics.CustomerID
ORDER BY Demographics.Spend DESC

To fix, replace the alias with the original column name -
SELECT LastName + ', ' + FirstName, TotalPurchaseYTD as Spend
FROM SalesIndividualCustomer Customer
INNER JOIN SalesIndividualDemographics Demographics
ON Customer.CustomerID = Demographics.CustomerID
ORDER BY Demographics.TotalPurchaseYTD DESC

Update , Jan 2011 , or as Nabeel points out in the comments - just don't use a table alias and column alias together in the ORDER BY clause.
SELECT LastName + ', ' + FirstName, TotalPurchaseYTD as Spend
FROM SalesIndividualCustomer Customer
INNER JOIN SalesIndividualDemographics Demographics
ON Customer.CustomerID = Demographics.CustomerID
ORDER BY Spend DESC

2 comments:

Nabeel said...

Hi,
This is not because of (YOU CAN NOT USE COLUMN ALIAS IN ORDER BY CLAUSE), but it's because use the table alias and column alias notation in the order by clause, if you remove the table alias notation in the order by clause it will work.

Thanks

r5d4 said...

thank you for your feedback.
post updated to reflect your comment.

r