SQL Server public expression mini-experience

Tag: des ORDER by const TLE JOIN NULL expression CLU Parent

Create a test data table

CREATE TABLE [dbo]. [Test] (
[ID] [int] IDENTITY (*) Not NULL,
[PARENT_ID] [INT] Null
[Agent] [Bit] Null
CONSTRAINT [pk_test] PRIMARY KEY CLUSTERED
(
[ID] ASC
) with (Pad_index = off, Statistics_norecompute = off, Ignore_dup_key = off, Allow_row_locks = on, allow_page_locks = ON) O N [PRIMARY]
) on [PRIMARY]

GO
INSERT [dbo]. [Test] ([id], [parent_id], [agent]) VALUES (1, 0, 1)
GO
INSERT [dbo]. [Test] ([id], [parent_id], [agent]) VALUES (2, 1, 0)
GO
INSERT [dbo]. [Test] ([id], [parent_id], [agent]) VALUES (3, 1, 1)
GO
INSERT [dbo]. [Test] ([id], [parent_id], [agent]) VALUES (4, 2, 0)
GO
INSERT [dbo]. [Test] ([id], [parent_id], [agent]) VALUES (5, 3, 1)
GO
INSERT [dbo]. [Test] ([id], [parent_id], [agent]) VALUES (6, 3, 0)
GO
INSERT [dbo]. [Test] ([id], [parent_id], [agent]) VALUES (7, 6, 0)
GO
INSERT [dbo]. [Test] ([id], [parent_id], [agent]) VALUES (8, 1, 0)
GO

Find the data you want with a common expression

With TEST_CTE
As (
Select *,10 as Tlevel from test where id = 7
UNION ALL
Select T.*,cte.tlevel-1 from Test as T
INNER join TEST_CTE as CTE on t.id = cte.parent_id
)

Select top 1 * from TEST_CTE
where agent=1
ORDER BY Tlevel Desc

SQL Server public expression mini-experience