Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | Order by Using Parent Child Relationship

In this SQL Puzzle we need order by using parent child relationship. Consider below records..

ProcessName Parent Child
ShareDrafts Job12 Job03
ShareDrafts Job03 Job81

So in the above case our first record has child = job03 then in the next row we need parent = Job03.

Please check out the sample input values and sample expected output below.

Sample Input

ProcessName Parent Child
ShareDrafts Job12 Job03
ShareDrafts Job13 Job58
ShareDrafts Job03 Job81
ShareDrafts Job58 NULL
ShareDrafts Job81 Job13
Deposits Job85 Job32
Deposits Job32 NULL
Deposits Job21 Job85

Expected Output

ProcessName Parent Child Level
ShareDrafts Job12 Job03 5
ShareDrafts Job03 Job81 4
ShareDrafts Job81 Job13 3
ShareDrafts Job13 Job58 2
ShareDrafts Job58 NULL 1
Deposits Job21 Job85 3
Deposits Job85 Job32 2
Deposits Job32 NULL 1

Script

Use below script to create table and insert sample data into it.

--

CREATE table X 
(
     ProcessName VARCHAR(50)
     ,Parent VARCHAR(50)
     ,Child VARCHAR(50)
)
GO

INSERT INTO X VALUES
     ('ShareDrafts','Job12','Job03')
     ,('ShareDrafts','Job13','Job58')
     ,('ShareDrafts','Job03','Job81')
     ,('ShareDrafts','Job58',NULL)
     ,('ShareDrafts','Job81','Job13')
     ,('Deposits', 'Job85', 'Job32')
     ,('Deposits', 'Job32', NULL)
     ,('Deposits', 'Job21', 'Job85')
GO

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution 1 , Using Numbers Table – Set Based Approach


--

;WITH CTE AS 
(
	SELECT *, 1 as Level FROM X where Child IS NULL
	UNION ALL
	SELECT b.*, a.level+1 
	FROM CTE a INNER JOIN X b ON a.ProcessName = b.ProcessName and a.Parent = b.Child
)
select * from CTE order by ProcessName DESC,level desc

--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

Advertisements