Solved

order a table based on column relationship (parent-child), can it be done?

Posted on 2016-11-30
4
48 Views
1 Endorsement
Last Modified: 2016-12-28
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:

1.png
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:

2.png
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!
1
Comment
Question by:Doua Vang
  • 2
4 Comments
 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
ID: 41908122
yes it is possible with a help of a recursive CTE:

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

Open in new window


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)

Open in new window

My advise for the future: try putting the workable DDE and insert queries of your sample data (see above) to speed up the response time. Pictures don't help us much. We are all busy as you are and typing these queries very often discourage us from answering the questions
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41908444
<<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

Open in new window

0
 

Author Closing Comment

by:Doua Vang
ID: 41909039
thank you! i apologize for the pictures and no syntax.
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41909539
Hi Doua,

I have simple solution without recursion. Please open a new question if you need it since this one is already closed.

Thank You!
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sql Query: need to find records in table_a with no matches in table_b 14 35
Running Total in Access 4 49
SQL View Syntax case etc 5 35
SQL Sub-Query Help 22 62
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question