SQL Server ORDER BY bug?

[Mario Menger]

Running the following query (on SQL Server 2000 SP3a)

SELECT
[Name] AS Blop,
Id AS [Name]
FROM
SYSOBJECTS
ORDER BY
SYSOBJECTS.[Name]

results in a set of records ordered by the Id column (aliased as Name),
surely it should order them by SYSOBJECTS.[Name] as I tell it to?

Is there any good reason why it would ignore the table name?

Cheers,
Rsa

[David Portas]

Re: SQL Server ORDER BY bug?

This is a known bug/feature. Because an ORDER BY statement can refer to
aliased columns as well as base table columns the column name is used
without reference to the alias (in fact the alias can be a non-existent one
and it won't generate an error).

The exception is where the specified column name is ambiguous (the same name
from more than one base table). Then the correct alias must be used.

--
David Portas
SQL Server MVP
--