Order SQL records based on matching values

Question

I need to sort the results of my query in a specific order. In my table every record has a unique Id. Some records are parent records which means the ParentId of a record can match with an Id of another record.

The sorting rules are a bit complex but I will do my best to explain.

The first record must be a record with ParentId='#'. Then all the records that belong to this record via the ParentId and their child records. After that the next record with the ParentId='#' and its child records (and also their child records).

Something like this:

Name              Id        ParentId

Report            L01_1     #
Finance           J13_4     L01_1
Finance Account   R45_3     J13_4
Billing           T66_5     J13_4
Cash Management   U67       L01_1
Sector-Reports    L07_7     #
Marketing         U34JK     L07_7
Market Analysis   LK89      U34JK
Giro              ZZ5       L07_7

Any ideas?

This is my current query, I tried to create a temporary table and sort of rank the "highest" parent records first (ParentId Value: #) but this didn't work either.

I am using SQL Server Management Studio.

CREATE TABLE #TempNumbering(
            Id VARCHAR(MAX),
            numbering INT
        );

        -- Numbering of records with Parent = ''#''
        INSERT INTO #TempNumbering (Id, numbering)
        SELECT Id, ROW_NUMBER() OVER (ORDER BY Id) AS numbering
        FROM #Result
        WHERE [Parent] = ''#'';


        -- Numbering of the other records based on the Parent
        INSERT INTO #TempNumbering(Id, numbering)
        SELECT r.Id, t.numbering
        FROM #Result r
        INNER JOIN #TempNumbering t ON r.[Parent] = t.Id;

        SELECT 
            39000 AS ''WFBLZ'', 
            tn.numbering,
            [Name],
            r.[Id],
            r.[Parent]
        FROM #Result r
        LEFT JOIN #TempNumbering tn ON r.Id = tn.Id


        ORDER BY numbering DESC
           
        DROP TABLE #TempNummerierung;

The output I get is:

Name              Id      ParentId

Report            L01_1   #
Finance           J13_4   L01_1
Cash Management   U67     L01_1
Finance Account   R45_3   J13_4
Billing           T66_5   J13_4
Sector-Reports    L07_7     #
Marketing         U34JK     L07_7
Giro              ZZ5       L07_7
Market Analysis   LK89      U34JK

Answer 1

You would need to use a recursive query and sort by the path of the recursion. Something like this should give you a good starting point:

select   
     sys_connect_by_path(id, ' -> ') path 
    , id
    , ParentID
    , Name  
 from 
      SORT_ORDER 
 START WITH ParentID = '#' 
 CONNECT BY PRIOR ID =  ParentID 
 order by path;