MySQL syntax for order by not accepted in SQL Server 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 &lt;= 1
ORDER BY node.lft;
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.