Error when field in ORDER BY does not match SELECT DISTINCT fields

This error crept up in SQL 2005 and does not appear in earlier versions of
SQL Server. The below code looks like:
SELECT DISTINCT 'Test' As TestName, ...
FROM ...
ORDER BY 'Test'
I had to change the ORDER BY to the alias TestName to get is to compile and
run in SQL2005. Is there a SQL command I can run to prior to adding my view
to prevent these messages? Or do I lump it and change all occurrences to us
e
the alias in the ORDER BY?
Thanks for your help,
TomOn Mon, 20 Mar 2006 10:27:30 -0800, Tom Kelley <Tom
Kelley@.discussions.microsoft.com> wrote:

>This error crept up in SQL 2005 and does not appear in earlier versions of
>SQL Server. The below code looks like:
>SELECT DISTINCT 'Test' As TestName, ...
>FROM ...
>ORDER BY 'Test'
>I had to change the ORDER BY to the alias TestName to get is to compile and
>run in SQL2005.
Hi Tom,
SQL Server 2000 was very forgiving WRT what it allowed you to put in an
ORDER BY clause. That had some great advantages, but some divantages
as well, since it allowed you to write rather ambiguous things, and it
would often interpret it different from what you meant. For instance, if
you still have access to a SQL Server 2000 installation, run the code
below and try to explain the results.
CREATE TABLE Persons
(FirstName varchar(20) NOT NULL,
LastName varchar(20) NOT NULL
)
go
INSERT INTO Persons (FirstName, LastName)
SELECT 'Hugo', 'Kornelis'
UNION ALL
SELECT 'Tom', 'Kelly'
go
SELECT LastName
FROM Persons
ORDER BY FirstName
go
SELECT LastName AS FirstName, FirstName AS LastName
FROM Persons
ORDER BY FirstName
go
SELECT LastName AS FirstName
FROM Persons
ORDER BY FirstName
go
SELECT LastName AS FirstName
FROM Persons
ORDER BY Persons.FirstName
go
SELECT LastName AS FirstName
FROM Persons
ORDER BY FirstName + ''
go
DROP TABLE Persons
go
Anyway, SQL Server 2005 is more strict (though not nearly as strict as
what the official ANSI standards for SQL allow <g> ). You might have to
change some ORDER BY clauses.

> Is there a SQL command I can run to prior to adding my view
>to prevent these messages? Or do I lump it and change all occurrences to u
se
>the alias in the ORDER BY?
The ORDER BY clause doesn't only accept the alias - it accepts column
names and expressions as well (though apparently not an "expression"
that is a string constant).
Though I personally fail to see ANY reason why you would want to include
a constant value in your ORDER BY list.
Hugo Kornelis, SQL Server MVPsql