Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.
Course Of The Month
9 days, 9 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
order a table based on column relationship (parent-child), can it be done?
Posted on 2016-11-30
hello everyone. i'm sure this can be done and i'm sure i can figure it out with enough time, however i'm asking all you experts out there for a quick reference.
i've got a table, 3 columns: ProcessName, Parent, Child
this table houses a process flow for a job (Parent) that starts then finishes and executes the next job (Child) then so forth. each time a job can start and execute another job, a record of the connection is represented.
this table is full of processes and parent jobs executing child jobs. to make it simple, i will use a simple table with simple data:
what i'm trying to do is have a select statement that orders the table by the process name descending, then order the jobs in which they execute. using the child to reference the parent and going backwards, i should be able to return the following result set:
notice that the process name is ordered by descending and that the first record of each process shows that a job fires off another job, then the next row, that job fires off another, and so forth until we hit NULL were the job doesn't fire off any job and stops.
i'm guessing i would have to join the table back into itself. any insights would be greatly appreciated it!
i've got a table, 3 columns: ProcessName, Parent, Child
this table houses a process flow for a job (Parent) that starts then finishes and executes the next job (Child) then so forth. each time a job can start and execute another job, a record of the connection is represented.
this table is full of processes and parent jobs executing child jobs. to make it simple, i will use a simple table with simple data:
what i'm trying to do is have a select statement that orders the table by the process name descending, then order the jobs in which they execute. using the child to reference the parent and going backwards, i should be able to return the following result set:
notice that the process name is ordered by descending and that the first record of each process shows that a job fires off another job, then the next row, that job fires off another, and so forth until we hit NULL were the job doesn't fire off any job and stops.
i'm guessing i would have to join the table back into itself. any insights would be greatly appreciated it!
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
2
4 Comments
Active 6 days ago
yes it is possible with a help of a recursive CTE:
Result:
declare @t table (ProcessName varchar(20),Parent varchar(20),Child varchar(20))
insert into @t values('ShareDrafts','Job12','Job03'),('ShareDrafts','Job13','Job58'),('ShareDrafts','Job03','Job81'),('ShareDrafts','Job58',NULL),('ShareDrafts','Job81','Job13')
;with a as (
select *, 1 as Level FROM @t where Child IS NULL
UNION ALL
SELECT b.*, a.level+1
FROM a INNER JOIN @t b ON a.ProcessName = b.ProcessName and a.Parent = b.Child)
select * from a order by level desc
Result:
ProcessName Parent Child Level
-------------------- -------------------- -------------------- -----------
ShareDrafts Job12 Job03 5
ShareDrafts Job03 Job81 4
ShareDrafts Job81 Job13 3
ShareDrafts Job13 Job58 2
ShareDrafts Job58 NULL 1
(5 row(s) affected)
Active 7 days ago
<<Nopts..>> Added ProcessName in the order by clause. <<Checking if it is possible with recursion>>
;with a as (
select *, 1 as Level FROM X where Child IS NULL
UNION ALL
SELECT b.*, a.level+1
FROM a INNER JOIN X b ON a.ProcessName = b.ProcessName and a.Parent = b.Child)
select * from a order by ProcessName DESC,level desc
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:
ht…
In part one, we reviewed the prerequisites required for installing SQL Server vNext.
In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month9 days, 9 hours left to enroll
623 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.