I would like to order in SQL server where the first line is the same, but later it’s different

The point in this case is that the starting point is the same for a line, let’s say a train line. The line starts on the same point, but ends on different end points. The services depends on the line where they are riding.

I’ve added the extra requested information, this could be extended with more lines.

Example table:

Initial situation:

Name    Next    Result
1000    1100    1
1100    1200    2
1100    2000    2
1200    1300    3
2000    3000    3
3000    4000    4
1300    1400    4
1400    1500    5
4000    5000    5

Expected result:

Name    Next    Result
1000    1100    1
1100    1200    2
1200    1300    3
1300    1400    4
1400    1500    5
1000    1100    1
1100    2000    2
2000    3000    3
3000    4000    4
4000    5000    5

If one extra row is added to the example input, the output would change as follows…

Example table:

Initial situation:

Name    Next    Result
1000    1100    1
1100    1200    2
1100    2000    2
1200    1300    3
2000    3000    3
3000    4000    4
1300    1400    4
1400    1500    5
4000    5000    5
4000    4100    5  -- Additional single row

Expected result:

Name    Next    Result
1000    1100    1
1100    1200    2
1200    1300    3
1300    1400    4
1400    1500    5
1000    1100    1
1100    2000    2
2000    3000    3
3000    4000    4
4000    5000    5
1000    1100    1  -- Everything from here is additional, caused by the one additional input row
1100    2000    2
2000    3000    3
3000    4000    4
4000    4100    5

Answer

You appear to be describing a tree structure…

           /- 1200 - 1300 - 1400
1000 - 1100
           - 2000 - 3000 - 4000

The results would then be both (all) paths from the root node.

WITH
  leaf AS
(
  SELECT *
    FROM nodes
   WHERE NOT EXISTS (SELECT *
                       FROM nodes AS lookup
                      WHERE lookup.name = nodes.next
                    )
),
  recursed_path AS
(
  SELECT
    *,
    next AS branch_name,
    0    AS level
  FROM
    leaf

  UNION ALL

  SELECT
    root.*,
    leaf.branch_name,
    level + 1
  FROM
    recursed_path  AS leaf
  INNER JOIN
    nodes          AS root
      ON root.next = leaf.name
)
SELECT
  *
FROM
  recursed_path
ORDER BY
  branch_name,
  level DESC

EDIT:

Minor corrections made, and this link to a working demo…

• https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=f027656a4a7fb2e1f19e7395762c75aa