Order of evaluation for sub-queries

[Steve Holdorf]

I am going through the msdn sql book and am running a very advanced query with a lot of sub-queries. In the book it does not explain the sub-queries in detail. It only shows the query and gives a result set. I am posting the query below so it can be seen. When I run it I am not getting the same results. First, I am not sure of the order of evaluation of each of the sub-queries (i.e. which get run first) and second I don't quite understand the table aliasing being used in the query. If some one could help me understand what is exactally going on, or point me in the right direction that would be great! Query below: SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate FROM Orders AS o1 WHERE OrderDate = (SELECT MAX(OrderDate) AS Expr1 FROM Orders AS o2 WHERE (EmployeeID = o1.EmployeeID) AND (RequiredDate = (SELECT MAX(RequiredDate) AS Expr1 FROM Orders AS o2 WHERE EmployeeID = o1.EmployeeID) AND (OrderDate = o1.OrderDate) AND (SELECT MAX(OrderID) AS Expr1 FROM Orders AS o2 WHERE (EmployeeID = o1.EmployeeID) AND ((OrderDate = o1.OrderDate))))))) I am not saying anything bad about the book but I am just learning advanced querying and someone who is an expert providing a little assistance would be great. Thanks, Steve

[Not Active]

It won't help immediately but a very good reference book for this subject is Inside Microsoft® SQL Server® 2008: T-SQL Querying[^]

---

only two letters away from being an asset

[i j russell]

Is this what you were trying to do?

SELECT o1.OrderID, o1.CustomerID, o1.EmployeeID, o1.OrderDate, o1.RequiredDate
FROM Orders AS o1
WHERE o1.OrderDate = (SELECT MAX(o2.OrderDate)
FROM Orders AS o2
WHERE o2.EmployeeID = o1.EmployeeID)
AND o1.RequiredDate = (SELECT MAX(o3.RequiredDate)
FROM Orders AS o3
WHERE o3.EmployeeID = o1.EmployeeID
AND o3.OrderDate = o1.OrderDate)
AND o1.OrderId = (SELECT MAX(o4.OrderID)
FROM Orders AS o4
WHERE o4.EmployeeID = o1.EmployeeID
AND o4.OrderDate = o1.OrderDate)

I find it much easier to read if I indent the code and explicitly use table alias'. The intent of the sql becomes much clearer.

[Steve Holdorf]

Your ordering has made it much more clear. Thanks for that! One last question. Do I read the subqueries from top to bottom or bottom to top. What is the order of execution? Much thanks, Steve Holdorf

[Mycroft Holmes]

There is no "order" invoked by the positioning of the subqueries. SQL will build an execution plan (you REALLY need to study and understand these to get some performance enhancements) that is based on the cost of each query. I have a base rule that says I use the strongest (returns the least row) filter first but I doubt it makes a difference.

[i j russell]

I use the same approach because it helps clarify my intent but as you say it actually doesn't have any impact upon the query. The Sql Server Query Optimizer will determine the most efficient route based on the latest statistics.