The reason I chose 3 and 4 , was because of ambiguity.

It would seem to me that since you have two employeeIDs , since you are joining them.

SELECT a.EmployeeID FROM HumanResources.Employee a

JOIN HumanResources.EmployeeAddress b

ON a.EmployeeID = b.EmployeeID

ORDER BY EmployeeID

But I guess since you don't select the second, alias b, you are fine not defining a in the order by clause.

Although you have to define 'a' in the SELECT clause.

If you were to select the ,b alias as such.

SELECT a.EmployeeID,b.EmployeeID FROM HumanResources.Employee a

JOIN HumanResources.EmployeeAddress b

ON a.EmployeeID = b.EmployeeID

ORDER BY a.EmployeeID

I feel it would be good practce to define your alias's in your order by, but I guess it is not a requirement, unless you select them, example would be with the *.

Totally did not even think of order by in the subquery problem with using the top N,tricky,tricky.

:hehe: