ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.
One of a set of tools we're offering as a way to say thank you for being a part of the community.
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!
2
4 Comments
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)
<<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.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs).
Being someone who is always looking for alternatives to "work your data", I came across this …
With just a little bit of SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form. If you have never written code or looked at an SQL statement before, no problem! ... give i…
Watch the software video of Kernel Import PST to Office 365 tools which can easily import PST and OST files to Office 365 for bulk mailboxes. The process of migration is simple and user can map source and destination mailboxes and easily import data…
Suggested Courses
Course of the Month4 days, 13 hours left to enroll
587 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.