Changes to order by in sql 2005

Hi, can anyone tell me why the following fails in sql 2005 with the error,
invalid column name 'title2'. This works in all other versions including 200
5
Express.
Select title as title1, title as title2 from title order by [title].title2
I can fix the query to run under 2005 by removing the field aliases, but I
can't find anything to explain why it failed. There are over 1000 queries in
my application, and although this is the only reported failure so far, if
there is a fundamental change regarding aliases or order by clauses, I need
to know so I can fix all affected queries.
ThanksPaul (Paul@.discussions.microsoft.com) writes:
> Hi, can anyone tell me why the following fails in sql 2005 with the
> error, invalid column name 'title2'. This works in all other versions
> including 2005 Express.
> Select title as title1, title as title2 from title order by [title].title2
If fails, because it's incorrect SQL. There is no title2 in title.
It was accepted in SQL 2000, yes. That was a bug.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Specifying a table alias in ORDER BY doesn't make sense, logically. From a l
ogical standpoint the
ORDER BY is performed as one of the last steps of the query (FROM, WHERE, GR
OUP BY, HAVING, SELECT
<col1, col2>, ORDER BY, TOP). Since ORDER BY is after the SELECT, we don't "
see" the base tables any
more, so it doesn't make sense to refer to them. In fact, at this stage, the
re exist only one table,
the table to be returned. But even SQL Server 2005 has some strangeness left
behind, even though it
is stricter than 2000:
--OK in 2000 and 2005
SELECT au_lname AS lname FROM authors AS a ORDER BY a.au_lname
--Fails in 2005, OK in 2000
SELECT au_lname AS lname FROM authors AS a ORDER BY a.lname
--Fails in 2000 and 2005
SELECT au_lname AS lname FROM authors AS a ORDER BY authors.au_lname
--Fails in 2005, OK in 2000
SELECT au_lname AS lname FROM authors AS a ORDER BY authors.lname
Anyhow, search for ORDER BY in below BOL URL (make sure you have the updates
BOL):
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/65eaafa1-9e06-4264-b547-
cbee8013c995.htm
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:FD1F5D8D-F11B-432C-A081-B75036B890A2@.microsoft.com...
> Hi, can anyone tell me why the following fails in sql 2005 with the error,
> invalid column name 'title2'. This works in all other versions including 2
005
> Express.
> Select title as title1, title as title2 from title order by [title].title2
> I can fix the query to run under 2005 by removing the field aliases, but I
> can't find anything to explain why it failed. There are over 1000 queries
in
> my application, and although this is the only reported failure so far, if
> there is a fundamental change regarding aliases or order by clauses, I nee
d
> to know so I can fix all affected queries.
> Thanks
>|||Paul wrote:
> Hi, can anyone tell me why the following fails in sql 2005 with the error,
> invalid column name 'title2'. This works in all other versions including 2
005
> Express.
> Select title as title1, title as title2 from title order by [title].title2
> I can fix the query to run under 2005 by removing the field aliases, but I
> can't find anything to explain why it failed. There are over 1000 queries
in
> my application, and although this is the only reported failure so far, if
> there is a fundamental change regarding aliases or order by clauses, I nee
d
> to know so I can fix all affected queries.
> Thanks
You need to read this section in Books Online:
http://msdn2.microsoft.com/en-us/library/ms143532(SQL.90).aspx
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks for the replies, at least I know what to check for in my other querie
s
now.
Thanks
"Paul" wrote:

> Hi, can anyone tell me why the following fails in sql 2005 with the error,
> invalid column name 'title2'. This works in all other versions including 2
005
> Express.
> Select title as title1, title as title2 from title order by [title].title2
> I can fix the query to run under 2005 by removing the field aliases, but I
> can't find anything to explain why it failed. There are over 1000 queries
in
> my application, and although this is the only reported failure so far, if
> there is a fundamental change regarding aliases or order by clauses, I nee
d
> to know so I can fix all affected queries.
> Thanks
>