Ambiguos of the ORDER BY

Previously I am using mssql2000
My sql is like this
SELECT mycol, mycol FROM mytable WHERE active=1 ORDER BY mycol
This query is running fine
Then recently I upgrade my mssql server to mssql2005
The query above return an error said "ambiguos"
Then I change my query to
SELECT mycol, mycol FROM mytable WHERE active=1 ORDER BY mytable.mycol
It works.
I am confuse of why the query can work fine on mssql2000 but cant work
on mssql2005
And why it can work after I change the
ORDER BY mycol
To
ORDER BY mytable.mycol
'
Can someone explain? Thank you!brian...@.gmail.com wrote:
> Previously I am using mssql2000
> My sql is like this
> SELECT mycol, mycol FROM mytable WHERE active=1 ORDER BY mycol
> This query is running fine
> Then recently I upgrade my mssql server to mssql2005
> The query above return an error said "ambiguos"
> Then I change my query to
> SELECT mycol, mycol FROM mytable WHERE active=1 ORDER BY mytable.mycol
> It works.
> I am confuse of why the query can work fine on mssql2000 but cant work
> on mssql2005
> And why it can work after I change the
> ORDER BY mycol
> To
> ORDER BY mytable.mycol
> '
> Can someone explain? Thank you!
SQL allows duplicate column names in query results. Usually the
duplicate names come from two different tables that are joined so if
you reference such a column without specifying a qualifying alias the
result is ambiguous because you may in fact be referring to either of
two different columns.
In your case the columns with duplicate names are in fact the same
column so it doesn't really matter which you refer to but SQL Server
2005 (rightly in my view) still insists that you qualify the name with
an alias. In SQL 2000 the ORDER BY didn't generate this error message,
which caused a lot of problems and confusion when duplicate names were
used.
Arguably the designers of SQL are to blame for allowing duplicate
column names at all. You would be well advised to modify your query to
return unique column names. Otherwise how will you refer to this data
in your client app? Relying on column positional indexes isn't good
practice.
SELECT mycol AS mycol1,
mycol AS mycol2
FROM mytable
WHERE active=1
ORDER BY mycol1;
Hope this helps.
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
--