archie flockhart (9/6/2011)
#3 got me; didn't know that it would identify the correct column automatically in the order by clause. I would always specify the table in these circumstances, and thought that omitting it would give the same error as #4 . But checking in BOL, and running the query, it does work OK, based on matching up a unique item in the SELECT clause.
That got me, too. Never tried to not specify the table, so wasn't aware this would work. Thanks for the lesson.
Not sure how consistent the definition is, though. BOL says:
In SQL Server, qualified column names and aliases are resolved to columns listed in the FROM clause. If order_by_expression is not qualified, the value must be unique among all columns listed in the SELECT statement.
But if I ORDER BY an unqualified fieldname from EmployeeAddress which is not listed in the Select , it still works. So it is allowing me to pick a field from a different table, without a table qualifier, even though the column is not listed in the SELECT.
As long as the table is included in the FROM section (directly or via a JOIN statement) and the column name (field name) is unique within all tables in the FROM clause, it will work. So while the wording in BOL leaves space for improvement, it is consistent.
Thanks for the question and the learning.
Michael