Suggested Videos:
Part 1 - How to find nth highest salary in sql
Part 2 - SQL query to get organization hierarchy
A lot of you have asked to explain, how a recursive CTE work line by line. If you have not watched Part 2 already, I strongly recommend to watch that video first before proceeding.
In Part 2 of SQL Server Interview questions and answers video series we discussed recursive CTE to retrieve the organization hierarchy.
We will be using Employees table to understand how a recursive CTE works.
Here is the recursive CTE that gets the organization hierarchy based on an EmployeeId. Notice that the recursive CTE has got 2 parts (Anchor & Recursive Member)
When David's EmployeeId is passed the query produces the following output
Let's now discuss how the CTE executes line by line.
Step 1: Execute the anchor part and get result R0
Step 2: Execute the recursive member using R0 as input and generate result R1
Step 3: Execute the recursive member using R1 as input and generate result R2
Step 4: Recursion goes on until the recursive member output becomes NULL
Step 5: Finally apply UNION ALL on all the results to produce the final output
Part 1 - How to find nth highest salary in sql
Part 2 - SQL query to get organization hierarchy
A lot of you have asked to explain, how a recursive CTE work line by line. If you have not watched Part 2 already, I strongly recommend to watch that video first before proceeding.
In Part 2 of SQL Server Interview questions and answers video series we discussed recursive CTE to retrieve the organization hierarchy.
We will be using Employees table to understand how a recursive CTE works.
![sql server query to display org chart](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgJqB_xMkPjR0Pf3rwZRB_IF6pt5eRY-0aP9575NxWqce3Xwa9F2Ugzi3d0sH4RPjVVzgQXYTpgi2gjn5QexxcuHAnLt3u0qd1fIwFygSJ7tFOlJ096kefGOSySNUJQTrZLLBe5WVoxljX8/s1600/sql+server+query+to+display+org+chart.png)
Here is the recursive CTE that gets the organization hierarchy based on an EmployeeId. Notice that the recursive CTE has got 2 parts (Anchor & Recursive Member)
Declare @ID int;
Set @ID = 7;
WITH
EmployeeCTE AS
(
-- Anchor
Select EmployeeId, EmployeeName,
ManagerID
From Employees
Where EmployeeId = @ID
UNION ALL
-- Recursive Member
Select Employees.EmployeeId ,
Employees.EmployeeName,
Employees.ManagerID
From Employees
JOIN EmployeeCTE
ON Employees.EmployeeId =
EmployeeCTE.ManagerID
)
Select E1.EmployeeName, ISNULL(E2.EmployeeName, 'No Boss') as ManagerName
From
EmployeeCTE E1
LEFT Join EmployeeCTE E2
ON E1.ManagerID = E2.EmployeeId
When David's EmployeeId is passed the query produces the following output
![how does a recursive cte work](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjFsrvcu0n6IJMm508nD1fJZTY8ibsPeWR1dGxgaAq3fc0Creci4-h81h6f8afIm253LUv6PoO0kMFIhHxQLSnVzt2EjtH55U-CKxBJlee7BJo9vnr-7kbiTVzkBHs0AQwCBP3XsxGB9tSg/s1600/organization+hierarchy+sql+server.png)
Let's now discuss how the CTE executes line by line.
Step 1: Execute the anchor part and get result R0
Step 2: Execute the recursive member using R0 as input and generate result R1
Step 3: Execute the recursive member using R1 as input and generate result R2
Step 4: Recursion goes on until the recursive member output becomes NULL
Step 5: Finally apply UNION ALL on all the results to produce the final output
![sql server interview questions and answers](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgUdyI6rAGvkGccSdm4iNm5Dx97tQbJHijcAHYuAXZKG8mwLbbQCyEdBGDMwcC14wdVvKLz2gtJPn-NqQfa-zcUFXE8qzLji-s5SLu4EwktC6sNne1f0ftnEjN5cTY1otuFacf6BAB3CDNo/s1600/sql+server+interview+questions+and+answers.png)
Hi Venkat,
ReplyDeleteNice explanation on Recursive CTE. I am curious about another way to solve the same. Pls suggest, if any.
if someone ask me that, what is a recursive query? in simple what is the answer for this question? kindly reply
ReplyDelete