SQL Server Aliased Column Inconsistent Behavior

Question

The following statements works on one instance of SQL Server 2005, but fails on another (also SQL Server 2005) with the following error:

A column has been specified more than once in the order by list. Columns in the order by list must be unique.

Does anyone know what would cause this? Here is a simplified version of the sql statement in question:

CREATE TABLE #foo (bar INT)


SELECT TOP 150 ID           
FROM                    
(   SELECT  bar as ID,bar FROM  #foo ) tmp
ORDER BY                bar,ID  

Answer 1

What does SELECT @@VERSION; say? The different behavior may be explained by a fix that was implemented in a service pack, for example. You should try to keep all of your environments consistent in this regard.