I need to order rows in my SQL Server table:

Into the table:

As you can see, the algorithm is as follows: starting from StateID = 1, you need to look at the value of the column NextStateID and put the row in which StateID = NextStateID after the previous row. And so, as long as there are such StateID.

Data:

DECLARE @t TABLE
           (
               StateID int,
               NextStateID int,
               Description varchar(255)
           )

INSERT INTO @t 
VALUES (874, 2, 'A'), 
       (1631, 3, 'B'),
       (935, 344, 'C'),
       (907, 813, 'D'),
       (2, 814, 'E'),
       (813, 874, 'F'),
       (1, 907, 'G'),
       (814, 935, 'H'),
       (344, 1631, 'I')

SELECT * FROM @t

I would be happy to get your suggestions.

CodePudding user response：

The easiest way is to use a recursrve CTE

declare @t table(
    StateID int,
    NextStateID int,
    Description varchar(255)
    );

insert into @t values (874, 2, 'A'), 
                      (1631, 3, 'B'),
                      (935, 344, 'C'),
                      (907, 813, 'D'),
                      (2, 814, 'E'),
                      (813, 874, 'F'),
                      (1, 907, 'G'),
                      (814, 935, 'H'),
                      (344, 1631, 'I');

WITH CTE AS 
  (select 0 as level,  StateID,NextStateID,Description from @t
  WHERE StateID = 1
      UNION ALL
    SELECT 
        level  1, 
e.StateID,e.NextStateID,e.Description
    FROM 
        @t e
        INNER JOIN CTE o 
            ON o.NextStateID = e.StateID
  )
SELECT  StateID,NextStateID,Description FROM CTE
ORDER BY level

fiddle