Order parent child records by parent group and children

Question

I need the results of a query to sort by specific two related columns. My table:

Row no | Col 1 | Col 2 | Col 3 | Col 4
    1  |   1   |  X    |  1    |  5
    2  |   2   |  Y    |  1    |  6
    3  |   5   |  Z    |  2    |  7
    4  |   6   |  T    |  2    |  0
    5  |   7   |  T    |  3    |  0
    6  |   6   |  W    |  2    |  0

The values ​​in Col 4represent the child record associated with Col 1.

So, for the Row no = 1next child entry is line 3, where it Col 1contains the value Col 4from the first line.

The next child line for line 3 is line 5, based on the link between Col 1and Col 4.

And I would like to return these results:

Row no | Col 1 | Col 2 | Col 3 | Col 4
    1  |   1   |  X    |  1    |  5
    3  |   5   |  Z    |  2    |  7
    5  |   7   |  T    |  3    |  0    
    2  |   2   |  Y    |  1    |  6
    4  |   6   |  T    |  2    |  0
    6  |   6   |  W    |  2    |  0 

So, I want the order to show the parent row and then its child rows before moving on to the next top level of the parent row.

Answer 1

, , CTE, .

:

CREATE TABLE #Table1
    (
      [Row no] INT ,
      [Col 1] INT ,
      [Col 2] VARCHAR(1) ,
      [Col 3] INT ,
      [Col 4] INT
    );

INSERT  INTO #Table1
        ( [Row no], [Col 1], [Col 2], [Col 3], [Col 4] )
VALUES  ( 1, 1, 'X', 1, 5 ),
        ( 2, 2, 'Y', 1, 6 ),
        ( 3, 5, 'Z', 2, 7 ),
        ( 4, 6, 'T', 2, 0 ),
        ( 5, 7, 'T', 3, 0 ),
        ( 6, 6, 'W', 2, 0 );

CTE:

;WITH    cte
          AS ( SELECT   * ,
                        ROW_NUMBER() OVER ( ORDER BY t1.[Col 1] ) GroupNo
               FROM     #Table1 t1
               WHERE    t1.[Col 1] NOT IN ( SELECT  [Col 4] FROM #Table1 )
               UNION ALL
               SELECT   t.* ,
                        cte.GroupNo
               FROM     #Table1 t
                        INNER JOIN cte ON cte.[Col 4] = t.[Col 1]
             )
    SELECT  *
    FROM    cte
    ORDER BY cte.GroupNo , cte.[Row no]

DROP TABLE #Table1

2 UNION ALL. , [Col 1] [Col 4]:

WHERE    t1.[Col 1] NOT IN ( SELECT  [Col 4] FROM #Table1 )

JOIN:

INNER JOIN cte ON cte.[Col 4] = t.[Col 1]

, a GroupNo, :

ROW_NUMBER() OVER ( ORDER BY t1.[Col 1] ) GroupNo