How to correctly build a generalized hierarchical expression?

Question

SQL Server

Hello, I have a salary table

and I'm trying to build a query that will display the id and salary for me, starting with the employee with the lowest salary and ending with the highest
one, but he writes that he is included in an infinite recursion (I set the border to 1000)
here is the request itself

WITH TestCTE(id, salary) 
AS 
( 
--Находим якорь рекурсии 
SELECT Employee_ID, 19000 as salary 
FROM Salary where Employee_ID = 4
UNION ALL 
--Делаем объединение с TestCTE (хотя мы его еще не дописали) 
SELECT s.Employee_ID, cte.salary + 1000 
FROM Salary s 
JOIN TestCTE cte ON s.Employee_ID = cte.id 
) 
SELECT * FROM TestCTE ORDER BY TestCTE.salary 
OPTION (MAXRECURSION 1000)

Answer 1

DROP TABLE IF EXISTS #SalaryData
CREATE TABLE #SalaryData (
  Id INT NOT NULL PRIMARY KEY IDENTITY,
  EmployeeId INT NOT NULL,
  SalaryDate DATETIME CONSTRAINT DF__SalaryData__SalaryDate DEFAULT (GETUTCDATE()),
  Salary DECIMAL(19,4) NOT NULL CONSTRAINT DF__SalaryData__Salary DEFAULT (0.0)
)

INSERT #SalaryData(EmployeeId, Salary)
VALUES
(2, 30000.0),
(1, 40000.0),
(5, 40000.0),
(3, 30000.0),
(4, 19000.0)

-- Просто выборка по зарплате
SELECT *
FROM #SalaryData sd
ORDER BY sd.Salary DESC

-- Выборка зарплат и сотрудников, у которых зарплата больше чем у целевого сотрудника
;WITH CTE AS
(
  SELECT
    RootEmployeeId = sd.EmployeeId,
    sd.EmployeeId,
    sd.Salary,
    Depth = 0
  FROM #SalaryData sd

  UNION ALL

  SELECT
    RootEmployeeId = cte.EmployeeId,
    sd.EmployeeId,
    sd.Salary,
    Depth = cte.Depth + 1
  FROM CTE cte
    INNER JOIN #SalaryData sd ON sd.Salary > cte.Salary
)
SELECT
  cte.RootEmployeeId,
  STRING_AGG(CONCAT(cte.Salary, ' (', cte.EmployeeId, ')'), ', ') WITHIN GROUP (ORDER BY cte.Salary ASC)
FROM CTE cte
WHERE cte.Depth < 2
GROUP BY cte.RootEmployeeId