MySQL syntax for order by not accepted in SQL Server

Question

I'm working through the SQL snippets for nested sets in this article by Mike Hillyer.

The article is using MySQL, but I'm using SQL Server. I get the following error reported when attempting to execute the query that should return the immediate subordinates of a node.

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

What do I need to change to get this query to work on MSSQL?

SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
 nested_category AS parent,
 nested_category AS sub_parent,
 (
 SELECT node.name, (COUNT(parent.name) - 1) AS depth
 FROM nested_category AS node,
 nested_category AS parent
 WHERE node.lft BETWEEN parent.lft AND parent.rgt
 AND node.name = 'PORTABLE ELECTRONICS'
 GROUP BY node.name
 ORDER BY node.lft
 )AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
 AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
 AND sub_parent.name = sub_tree.name
GROUP BY node.name
HAVING depth <= 1
ORDER BY node.lft;

• Aaron Bertrand: Why does your subquery need an order by? Did you try just removing it? I don't understand what possible point it serves, even if you do add the TOP hack. Of course, what do you expect to happen when you have more than one row for any given node?
• ypercubeᵀᴹ: The query seems very inefficient. There are probably much better ways to write it, in both MySQL and SQL Server.
• Michael Vincent: @AaronBertrand thanks for your comment, I will try and remove the order by. I can't exactly say I understand the query (yet) I just copied it from the website I referenced. The website references the Joe Celko book on trees and hierarchies which seems to be the "go-to" book for this subject.
• Michael Vincent: Thanks for your comment, @ypercubeTM. Care to have a go at re-writing it? :)
• Rick James: @AaronBertrand - In the past, MySQL would use the subquery's ORDER BY to feed items to the outer query in a particular order. That made "groupwise max" work quite easily and efficiently. But the standard says there is no order in a subquery, so now MySQL (and, I assume MSSQL) ignores the ORDER BY. The "rgt-lft" algorithm dates back more than a decade in MySQL docs.

Answer 1

Your [sub_tree] derived table is using an ORDER BY with no TOP operator.

Try something like the following:

 SELECT TOP (1) node.name
 [...]
 ORDER BY node.lft
) AS sub_tree

Granted that you are just following a MySQL book, I should probably mention the whole "no-no" on doing old style joins on SQL Server. Use INNER JOIN, LEFT JOIN, etc rather than FROM a,b,c WHERE a.id =b.id.