Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  General SQL Server Forums  New to SQL Server Programming  Parent-Child Query and Order By

Author  Topic  MevaD Starting Member 32 Posts Posted - 2010-09-27 : 14:23:03 Hi, Is it possible to order my results by Expected Completion Date of the Root Task and still maintain the Parent-Child list? Please see below: Using SQL 2005 The Table: Task taskID int, parentID int, projID int, title char(30) expCompDate datetime The Query: SELECT Task.TaskID, Task.ParentTaskID, t2.ParentTaskID AS [GrandParent], Task.Title, t3.ExpCompDate FROM pt_Task LEFT JOIN Task t2 on Task.ParentTaskID = t2.TaskID LEFT JOIN Task t3 on Task.TaskID = t3.TaskID and t3.ParentTaskID is NULL WHERE Task.ProjID = @projID Order By Coalesce(t2.ParentTaskID, pt_Task.ParentTaskID, Task.TaskID), Coalesce(Task.ParentTaskID, Task.TaskID), Coalesce(Task.ParentTaskID, 0) Current Results: 7 NULL NULL Task 7 2010-11-30 00:00:00.000 10 7 NULL Task 10 NULL 11 NULL NULL Task 11 2010-10-31 00:00:00.000 9 11 NULL Task 9 NULL 13 11 NULL Task 13 NULL 14 13 11 Task 14 NULL 12 NULL NULL Task 12 1900-01-01 00:00:00.000 -------------------------------------------------- This is what I would like to get as a result set (Ordered By ExpCompDate AND Parents and children still together.) 12 NULL NULL Task 12 1900-01-01 00:00:00.000 11 NULL NULL Task 11 2010-10-31 00:00:00.000 9 11 NULL Task 9 NULL 13 11 NULL Task 13 NULL 14 13 11 Task 14 NULL 7 NULL NULL Task 7 2010-11-30 00:00:00.000 10 7 NULL Task 10 NULL Thanks!