How can I sort this?

• webskater

  SSCarpal Tunnel

  Points: 4214

  More actions

  October 24, 2012 at 11:01 am

  #266110

  CREATE TABLE #tblTasks

  (

  TaskID int,

  BaseTaskID int

  )

  GO

  INSERT INTO #tblTasks (TaskID, BaseTaskID)

  SELECT 1, 0 UNION ALL

  SELECT 2, 0 UNION ALL

  SELECT 3, 0 UNION ALL

  SELECT 4, 2 UNION ALL

  SELECT 5, 0 UNION ALL

  SELECT 6, 4

  What this means is that TaskID 1 is stand alone, TaskID 2 is stand alone, TaskID 3 is stand alone, TaskID 4 relates to Task 2, TaskID 5 is stand alone, Task 6 relates to Task 4

  How can I select (sort) the data so it gets returned in order of TaskID like this:

  1

  2

  4

  6

  3

  5

  i.e. 4 comes after 2 because it is related to it, and 6 comes after 4 because is related to it - then we pick up again with 3, miss out 4 because it has already been used and so on.

  Thanks for any help.

• T_Dot_Geek

  SSC-Addicted

  Points: 408

  More actions

  October 24, 2012 at 1:34 pm

  #1552404

  Create a new calculated SortID column and add it in order by clause

  SELECT TaskID, BaseTaskID, SortID = Case When BaseTaskID = 0 THEN TaskID ELSE BaseTaskID END

  order by SortID

• CapnHector

  SSCoach

  Points: 16143

  More actions

  October 24, 2012 at 2:53 pm

  #1552438

  T_Dot_Geek (10/24/2012)

  ---

  Create a new calculated SortID column and add it in order by clause

  SELECT TaskID, BaseTaskID, SortID = Case When BaseTaskID = 0 THEN TaskID ELSE BaseTaskID END

  order by SortID

  thats close but you also need to add task id as well. also you can just move the case into the order by clause

  SELECT TaskID, BaseTaskID

  FROM #tblTasks

  order by Case When BaseTaskID = 0 THEN TaskID ELSE BaseTaskID END, TaskID

  ---

  For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

  For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

  Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
  Jeff Moden's Cross tab and Pivots Part 1[/url]
  Jeff Moden's Cross tab and Pivots Part 2[/url]

• T_Dot_Geek

  SSC-Addicted

  Points: 408

  More actions

  October 24, 2012 at 3:11 pm

  #1552445

  capnhector (10/24/2012)

  ---

  T_Dot_Geek (10/24/2012)

  ---

  Create a new calculated SortID column and add it in order by clause

  SELECT TaskID, BaseTaskID, SortID = Case When BaseTaskID = 0 THEN TaskID ELSE BaseTaskID END

  order by SortID

  thats close but you also need to add task id as well. also you can just move the case into the order by clause

  SELECT TaskID, BaseTaskID

  FROM #tblTasks

  order by Case When BaseTaskID = 0 THEN TaskID ELSE BaseTaskID END, TaskID

  It doesn't make any difference if you add taskid or not. It will produce the same result as below.

  TaskIDBase SortID

  TaskID

  101

  202

  422

  303

  644

  505

• T_Dot_Geek

  SSC-Addicted

  Points: 408

  More actions

  October 24, 2012 at 3:16 pm

  #1552446

  Unfortunately, both logic not giving a desired order.

• CapnHector

  SSCoach

  Points: 16143

  More actions

  October 24, 2012 at 3:23 pm

  #1552448

  T_Dot_Geek (10/24/2012)

  ---

  capnhector (10/24/2012)

  ---

  T_Dot_Geek (10/24/2012)

  ---

  Create a new calculated SortID column and add it in order by clause

  SELECT TaskID, BaseTaskID, SortID = Case When BaseTaskID = 0 THEN TaskID ELSE BaseTaskID END

  order by SortID

  thats close but you also need to add task id as well. also you can just move the case into the order by clause

  SELECT TaskID, BaseTaskID

  FROM #tblTasks

  order by Case When BaseTaskID = 0 THEN TaskID ELSE BaseTaskID END, TaskID

  It doesn't make any difference if you add taskid or not. It will produce the same result as below.

  TaskIDBase SortID

  TaskID

  101

  202

  422

  303

  644

  505

  on these small data volumes it may not seem to make a difference however there is no order with out ORDER BY.

  the Sql query engine returns rows in the order it is easiest for the engine to return them in but if we want an explicit order as above we have to specify the column TaskID to ensure the order. If the OP needs to scale this up to a table with a million records and there is no ORDER BY TaskID the order could be "Wrong" (wrong in quotes because its not wrong just unexpected)

  EDIT: and to the OP you are correct. i posted the correction to T_Dot_Geek with out checking his code close enough. for that i am sorry and am working on the issue.

  ---

  For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

  For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

  Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
  Jeff Moden's Cross tab and Pivots Part 1[/url]
  Jeff Moden's Cross tab and Pivots Part 2[/url]

• webskater

  SSCarpal Tunnel

  Points: 4214

  More actions

  October 24, 2012 at 4:08 pm

  #1552457

  Thanks for the input so far - as you say, the code so far does not produce the right order.

  I've been playing with an awful mess of cursors and temporary tables ... but I'm hoping there is a more sane way to achieve it.

• CapnHector

  SSCoach

  Points: 16143

  More actions

  October 24, 2012 at 4:14 pm

  #1552462

  sku370870 (10/24/2012)

  ---

  Thanks for the input so far - as you say, the code so far does not produce the right order.

  I've been playing with an awful mess of cursors and temporary tables ... but I'm hoping there is a more sane way to achieve it.

  i have it but it uses a recursive cte so it may not be very fast if you run it over a large number of rows.

  there may still be another way to do this but if there is im not seeing it right now.

  WITH rCTE AS (

  SELECT TaskID, BaseTaskID, TaskID AS RootTask

  FROM #tblTasks

  WHERE BaseTaskID = 0

  UNION ALL

  SELECT a.TaskID, a.BaseTaskID, rCTE.RootTask

  FROM #tblTasks a

  INNER JOIN rCTE

  ON a.BaseTaskID = rCTE.TaskID

  )

  SELECT *

  FROM rCTE

  ORDER BY RootTask, TaskID

  EDIT Made the code pretty.

  ---

  For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

  For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

  Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
  Jeff Moden's Cross tab and Pivots Part 1[/url]
  Jeff Moden's Cross tab and Pivots Part 2[/url]

• webskater

  SSCarpal Tunnel

  Points: 4214

  More actions

  October 24, 2012 at 4:27 pm

  #1552465

  Thanks very much - as you say, that does the job.

  Worst case will be maybe 500 rows - so I'm sure performance won't be an issue.

  Thanks again.

• CapnHector

  SSCoach

  Points: 16143

  More actions

  October 24, 2012 at 4:39 pm

  #1552469

  sku370870 (10/24/2012)

  ---

  Thanks very much - as you say, that does the job.

  Worst case will be maybe 500 rows - so I'm sure performance won't be an issue.

  Thanks again.

  this is sql server, nothing is sure until you test and then only until you add more data.

  ---

  For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

  For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

  Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
  Jeff Moden's Cross tab and Pivots Part 1[/url]
  Jeff Moden's Cross tab and Pivots Part 2[/url]

• T_Dot_Geek

  SSC-Addicted

  Points: 408

  More actions

  October 26, 2012 at 8:35 am

  #1553210

  Salute Sir capnhector

  It took me a while to understand your final solution, however, could not understand fully 🙂

  Thank you,

  T_Dot_Geek