1

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;
5
  • 2
    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? Commented May 20, 2016 at 14:48
  • The query seems very inefficient. There are probably much better ways to write it, in both MySQL and SQL Server. Commented May 20, 2016 at 16:24
  • @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. Commented May 23, 2016 at 8:05
  • Thanks for your comment, @ypercubeᵀᴹ. Care to have a go at re-writing it? :) Commented May 23, 2016 at 8:06
  • @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.
    – Rick James
    Commented May 30, 2016 at 20:26

1 Answer 1

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.