How to Order By within an Order By in SQL?

Question

I'm trying to create a SQL statement which will recreate the hierarchical container/folder/test structure in SilkCentral Test Manager.

• Test containers have no ParentID
• Test folders contain a ParentID and IsLeaf = 0
• Tests contain a ParentID and IsLeaf = 1

This Query results in all of the test containers, folders, and tests:

SELECT "NodeID", "ParentID", "Name", "IsLeaf", "OrderNumber"
FROM "Silk"."TM_TestPlanNodes" AS TPN
WHERE PROJECTID = 36
ORDER BY "ParentID", "OrderNumber", "IsLeaf"

Here are some of the Results:

NodeID  ParentID    Name                        IsLeaf  OrderNumber 
65408               Installation and Upgrades   0       0   
65445               Connectivity                0       1   
65448               Focus                       0       2   
65409               GINA / PLAP                 0       3   
65446               Graphical User Interface    0       4   
71038               Login Properties            0       5   
65449               Miscellaneous               0       6   
70636               Net Firewall                0       7   
70998               Software Updates            0       8   
65447               Third-party Services        0       9   
70805               SilkTest Automated Tests    0       10  
68812   65408       0. Setup                    0       0
65454   65408       1. Installations & Upgrades 0       1   
65450   65408       Typical/Custom Installation 0       2   

I would like this ordering instead:

The ParentID is sorted, but if there exists a Node with the ParentID=thePreviousNode'sID, then that is chosen next. If there are multiple of those nodes, they should be ordered by IsLeaf and then, OrderNumber.

How to accomplish this? I'm very limited in what I can do, because I think very complicated syntax will end up throwing errors in Silk. I was going to try a nested SELECT statement:

SELECT "NodeID", "ParentID", "Name","IsLeaf" 
FROM "Silk"."TM_TestPlanNodes" 
WHERE PROJECTID = '36'AND ParentID LIKE (
  SELECT ParentID 
  FROM "Silk"."TM_TestPlanNodes" 
  WHERE NAME = 'Installation and Upgrades')

But this is getting this error: "Could not execute report query: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

This is why I'm fiddling with Order By.

Answer 1

Although it might not be a PERFECT fit, it is very close with a nested hierarchical representation of parent-child records in a self-joined list and incorporated proper ordering concerns. You may have to tweak it a bit for your table, but here's a link to a prior solution

To clarify that problem with the menu and the corresponding data.

id    |     parentid     |       name
1     |        0         |      CatOne
2     |        0         |      CatTwo
3     |        0         |      CatThree
4     |        1         |      SubCatOne
5     |        1         |      SubCatOne2
6     |        3         |      SubCatThree

Desired output

CatOne 1
--SubCatOne 4
--SubCatOne2 5
CatTwo 2
CatThree 3
--SubCatThree 6

The FIRST case is pre-grouping all the like ID's based on the parent... So, when the parent ID is 0, it IS the top-most level, so we keep it's ID. Then, any children under it, we want their respective PARENT IDs so all of the same are correctly pre-grouped.

The purpose of the SECOND group by is to force the entry that represents the actual TOP LEVEL menu item to the top of the list regardless of the child entries.

Say you have a table where IDs are already established, and you now add a new item into position ID = 7 for "New Top Level" and want to move ID #s 2 and 3 into the new "top-level section. If you just to the query with the first CASE, your records would be simulated returned as

ID   Parent   Name  (natural order from the table)
2    7        CatTwo
3    7        CatThree
7    0        New Category.  (we want THIS one in FIRST POSITION of the group)

As you can see, this would be a bad representation of the sub-grouping order. The top-level item actually is in the 3rd position... To bring it to the front, we are now sub-grouping and saying... if the Parent ID of the record = 0, then sort it as if it were a '1' priority. Anything else is considered a '2' priority and would simulate the result like

ID   Parent   Name             SubPrioritySort
7    0        New Category.    1
2    7        CatTwo           2
3    7        CatThree         2

Since you are not actually returning these "CASE" values in your result query, you wouldn't otherwise visually see it... but for grins, add them as columns to your query to see the impact. Hopefully this clarified the answer for you.

In your question, you would obviously be able to add your sort order column to the basis of this query.

Answer 2

You can use a recursive cte to create a hidden column and orderby that column. The hidden column should be Something like:

WITH cte (NodeID, ParentID, Name, IsLeaf, [Order])
AS
(
    SELECT NodeID, ParentID, Name, IsLeaf, cast(NodeID as nvarchar(10))
    FROM "Silk"."TM_TestPlanNodes" 
    WHERE PROJECTID = '36'
    UNION ALL
    SELECT "NodeID", "ParentID", "Name","IsLeaf", cast(leftNode.ParentID as nvarchar(10)) + cast(leftNode.NodeID as nvarchar(10))
    FROM "Silk"."TM_TestPlanNodes" as leftNode
    INNER JOIN cte on cte.NodeID = leftNode.ParentID
    WHERE leftNode.ParentID = cte.NodeID
)
select  "NodeID", "ParentID", "Name","IsLeaf"  from cte
order by cast([Order] as nvarchar(50))

This was written in notepad so is possible to have some errors, but the idea is to make an [order] column that for example for 65530 would be 654086554569530 (the parent_parent, the parent and the node)

EDIT:

this only works if the ids are all 5 characters long, but from here you can make the proper tweaks.