finally, i designed a query that returns the desired results. its the following one.
create table products(product_id int, next_id int, product_name nvarchar(50));
insert into products(product_id, next_id, product_name)
Values (1, null, N'product1')
,(2, 4, N'product2')
,(3, 2, N'product3')
,(4, 1, N'product4')
,(5, 3, N'product5');
WITH LinkedList (product_id, next_id, product_name, Level)
AS
(
-- Anchor member definition
SELECT e.product_id, e.next_id, e.product_name,
0 AS Level
FROM products AS e
WHERE e.next_id IS NULL
UNION ALL
-- Recursive member definition
SELECT e.product_id, e.next_id, e.product_name,
Level + 1
FROM products AS e
INNER JOIN LinkedList AS d
ON e.next_id = d.product_id
)
-- Statement that executes the CTE
SELECT product_id, next_id, product_name
FROM LinkedList
Order by Level desc;
drop Table products;
Help people,so poeple can help you.
