trying to order an alert i created that sends out blocking spids

• Snargables

  SSCrazy Eights

  Points: 8855

  More actions

  May 11, 2016 at 2:09 pm

  #326300

  IF Object_id('tempdb..#blocked') IS NOT NULL

  DROP TABLE #blocked

  select '201' spid,'200' BlockingSPID

  into #blocked

  union all

  select '199','202'

  union all

  select '202','0'

  union all

  select '200','0'

  union all

  select '568','831'

  union all

  select '819','846'

  union all

  select '831','1051'

  union all

  select '1022','1051'

  union all

  select '1056','831'

  union all

  select '846','831'

  union all

  select '1051','0'

  union all

  select '733','798'

  union all

  select '798','831'

  union all

  select '939','798'

  select *

  from #blocked

  Current output

  201200

  199202

  2020

  2000

  568831

  819846

  8311051

  10221051

  1056831

  846831

  10510

  733798

  798831

  939798

  Desired Output

  2000

  201200

  2020

  199202

  10510

  8311051

  10221051

  568831

  798831

  846831

  1056831

  733798

  939798

  819846

• Phil Parkin

  SSC Guru

  Points: 246960

  More actions

  May 11, 2016 at 2:17 pm

  #1878388

  What is the logic behind your desired ordering?

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  May 11, 2016 at 3:13 pm

  #1878401

  It looks like he wants to sort by the root process. He has an adjacency list of processes and blocking processes. Take a look at the following article Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets[/url]. The code includes a SortPath, which is what you will want to sort on. The main issue that you will find is that you may have cycles, so you will want to limit the depth of your search.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA