Q:

What SQL query should I perform to get the result set expected?

What SQL query should I perform to get the result set expected, giving the first element of the chain (2) as input data, or any of them ?
table name: changes
+----+---------------+---------------+
| id | new_record_id | old_record_id |
+----+---------------+---------------+
|   1|              4|              2|
|      -- non relevant data --       |
|   6|              7|              4|
|      -- non relevant data --       |
|  11|             13|              7|
|  12|             14|             13|
|      -- non relevant data --       |
|  31|             20|             14|
+----+---------------+---------------+

Result set expected:

+--+
| 2|
| 4|
| 7|
|13|
|14|
|20|
+--+

I know I should consider change my data model, but: What if I couldn't?
Thank you in advance!

A:

Following code should work to get your result
CREATE TABLE #return(val int)
DECLARE @VAL int
SET @VAL = 2

WHILE (SELECT count(*) FROM [changes]
WHERE old_record_id = @VAL) > 0
BEGIN
INSERT INTO #return values(@VAL)

SELECT @VAL = new_record_id FROM [changes]
WHERE old_record_id = @VAL
END
INSERT INTO #return values(@VAL)
SELECT * FROM #return

