Рекурсивный CTE и как преодолеть ошибку циклической ссылки

В основном вы были там... вам просто нужно было внести два изменения:

1. Добавьте дополнительное условие соединения q.ID > u.Id, чтобы оно остановилось, когда вы достигнете наименьшего идентификатора.
2. Подсчитайте, какие результаты показать, чтобы включить в них те, по которым произошел отказ. Я нашел более понятным добавить конкретный расчет для определения корня, чем усложнять логику предложения where.

WITH q AS (
    SELECT 0 lvl
        , ID
        , PreviousID
        , PreviousID LastId
        , Location
        , Location AS OriginalLocation
        , CONVERT(bit, CASE WHEN PreviousID IS NULL OR PreviousId > Id THEN 1 ELSE 0 END) IsRoot
    FROM @IDs
    WHERE ID in (select OrderID from @ORDERID) 
  
    UNION ALL 
  
    SELECT lvl + 1
        , q.ID
        , u.PreviousId
        , q.PreviousId LastId
        , q.Location
        , u.Location
        , CONVERT(bit, CASE WHEN u.PreviousID IS NULL OR u.PreviousId > u.Id THEN 1 ELSE 0 END) IsRoot
    FROM q
    INNER JOIN @IDs u ON u.ID = q.PreviousID
        -- Ensure we stop at the lowest Id
        AND q.ID > u.Id
)
SELECT lvl, ID, coalesce(LastId,Id) OriginalId, Location, OriginalLocation
FROM q
WHERE IsRoot = 1
ORDER BY id;

DBFiddle

Как отмечено в комментариях ниже, это не дает желаемого результата «наименьшего числа» ОП. Я пока оставляю его опубликованным, ожидая дальнейшего обсуждения и возможного улучшения. Другой опубликованный ответ может быть лучшим простым ответом.

Я собираюсь предположить общий случай, когда порядок ID не имеет значения, а последовательность значений PreviousID может произвольно прыгать вверх и вниз. Я также допускаю случаи, когда последовательность может развиваться линейно, прежде чем перейти в цикл, который может включать или не включать исходное значение идентификатора.

Один из способов обнаружения петель — создать список значений посещенных идентификаторов и сверить каждый новый идентификатор со списком посещенных идентификаторов. Поскольку в SQL Server нет массивов или подобных многозначных типов, мы можем вернуться к списку посещенных идентификаторов, разделенному запятыми, который хранится в виде строки. Этот список будет инициализирован исходным значением идентификатора, а дополнительные идентификаторы будут добавлены в рекурсивную часть CTE. Тест CHARINDEX() с соответствующим разделением может проверить, достигли ли мы ранее посещенного идентификатора.

Что-то вроде:

WITH q AS (
    SELECT
        0 lvl, ID, PreviousID, PreviousID LastId
        ,Location, Location as OriginalLocation
        ,CAST(CONCAT(',', ID, ',') AS VARCHAR(MAX)) as VisitedIDs
        ,CAST(0 AS BIGINT) AS LoopDetected
    FROM    IDs
    where ID in (select OrderID from ORDERID) 
    UNION ALL 
    SELECT lvl+1, q.ID,u.PreviousId,q.PreviousId LastId
       ,q.Location,u.Location
       ,CONCAT(VisitedIDs, u.ID, ',')
       ,CHARINDEX(CONCAT(',', u.ID, ','), VisitedIDs) AS LoopDetected
    FROM    q
    INNER JOIN IDs u ON u.ID = q.PreviousID
    WHERE q.LoopDetected = 0
)
select lvl, ID, coalesce(LastId,Id) OriginalId,Location,OriginalLocation
    , q.LoopDetected, q.VisitedIDs
    , CASE WHEN q.LoopDetected > 0 THEN 'Loop detected' ELSE '' END AS ErrorCheck
from q
where (PreviousId is null OR q.LoopDetected > 0)
order by id, lvl;

Обратите внимание, что тест CHARINDEX() заключает значение идентификатора в запятую, чтобы гарантировать, например, что ID = 5 соответствует только ...,5,..., а не ...,15,... или ...,51,.... В VisitedIDs также есть дополнительные запятые для поддержки этого теста.

Примерные результаты (с некоторыми дополнительными данными испытаний):

Если вы не хотите создавать список, вы можете отслеживать образец предыдущего идентификатора (первоначально начальный идентификатор) и сверять каждый шаг с этим отслеживаемым идентификатором. Проблема в том, что может быть достигнут цикл, не включающий исходный идентификатор. Чтобы справиться с этим случаем, вы можете сбрасывать отслеживаемый идентификатор через увеличивающиеся интервалы (степени 2), чтобы при входе в петлю отслеживаемый идентификатор в конечном итоге сбрасывался, и петля в конечном итоге была обнаружена.

WITH q AS (
    SELECT
        0 lvl, ID, PreviousID, PreviousID LastId
        ,Location, Location as OriginalLocation
        ,ID as LoopCheckID
    FROM    IDs
    where ID in (select OrderID from ORDERID) 
    UNION ALL 
    SELECT lvl+1, q.ID,u.PreviousId,q.PreviousId LastId
       ,q.Location,u.Location
       ,CASE WHEN lvl & (lvl - 1) = 0 THEN u.ID ELSE q.LoopCheckID END
    FROM    q
    INNER JOIN IDs u ON u.ID = q.PreviousID
    WHERE q.PreviousID <> q.LoopCheckID
)
select lvl, ID, coalesce(LastId,Id) OriginalId,Location,OriginalLocation
    , q.PreviousID, q.LoopCheckID
    , CASE WHEN q.PreviousID = q.LoopCheckID THEN 'Loop detected' ELSE '' END AS ErrorCheck
from q
where (PreviousId is null OR q.PreviousID = q.LoopCheckID)
order by id, lvl;

lvl & (lvl - 1) = 0 — это сложная проверка манипуляции с битами, которая верна, когда lvl является степенью 2 (1, 2, 4, 8, 16, ...).

Примеры результатов:

Обратите внимание, что в нескольких случаях, описанных выше, обнаружение цикла происходило на несколько шагов позже, чем в предыдущем решении на основе списка.

См. эту db<>fiddle для демонстрации обоих вышеперечисленных методов.

Еще один возможный подход — заранее проверить всю таблицу IDs, чтобы определить, какие идентификаторы доступны (в обратном направлении) из строк, имеющих PreviousID = null. Как только этот список будет построен, любые идентификаторы, не входящие в этот список, будут признаны частью цикла. (Я не кодировал этот подход.)