How to sort a sql result based on values in previous row?

Question

I'm trying to sort a sql data selection by values in columns of the result set. The data looks like:

(This data is not sorted correctly, just an example)

ID      projectID   testName                                objectBefore    objectAfter
=======================================================================================
13147   280         CDM-710 Generic TP-0000120 TOC~~@~~     -1              13148
1145    280         3.2 Quadrature/Carrier Null 25 Deg C    4940            1146
1146    280         3.2 Quadrature/Carrier Null 0 Deg C     1145            1147
1147    280         3.3 External Frequency Reference        1146            1148
1148    280         3.4 Phase Noise 50 Deg C                1147            1149
1149    280         3.4 Phase Noise 25 Deg C                1148            1150
1150    280         3.4 Phase Noise 0 Deg C                 1149            1151
1151    280         3.5 Output Spurious 50 Deg C            1150            1152
1152    280         3.5 Output Spurious 25 Deg C            1151            1153
1153    280         3.5 Output Spurious 0 Deg C             1152            1154
............
18196   280         IP Regression Suite                     18195           -1

The order of the data is based on the objectBefore and the objectAfter columns. The first row will always be when objectBefore = -1 and the last row will be when objectAfter = -1. In the above example, the second row would be ID 13148 as that is what row 1 objectAfter is equal to. Is there any way to write a query that would order the data in this manner?

This is SQL Server 2008. I don't have a choice in how the data is structured, I'm just trying to create a report from it. — Austin, Dec 06 '13 at 23:17

score 3 · Accepted Answer · edited May 23 '17 at 10:25

This is actually sorting a linked list:

WITH SortedList (Id, objectBefore , projectID, testName, Level)
AS
(
  SELECT Id, objectBefore , projectID, testName, 0 as Level
    FROM YourTable
   WHERE objectBefore = -1
  UNION ALL
  SELECT ll.Id, ll.objectBefore , ll.projectID, ll.testName, Level+1 as Level
    FROM YourTable ll
   INNER JOIN SortedList as s
      ON ll.objectBefore = s.Id
)

SELECT Id, objectBefore , projectID, testName
  FROM SortedList
 ORDER BY Level

You can find more details in this post

How to sort a sql result based on values in previous row?

1 Answers1