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