Ordering items by hierarchy without RBAR

• kramaswamy

  SSCoach

  Points: 18181

  More actions

  March 24, 2014 at 1:40 pm

  #289727

  Hey all,

  I feel like I already know the answer to this question, but it is eluding me ...

  Say I have a table with two columns - ID and ParentID. Say this table has the following values:

  IDParentID

  1050

  2030

  3010

  50NULL

  Now, say I want the following output from my query:

  IDParentIDOrder

  10502

  20304

  30103

  50NULL1

  Is there any way to get this result in a single query, without needing potentially infinite recursion or using a row-by-row comparaison?

• Jeff Moden

  SSC Guru

  Points: 1004589

  More actions

  March 24, 2014 at 2:32 pm

  #1699679

  Sure... you could do a 4 way self join but that's hardcoding levels.

  Recursion isn't so bad here. It will do things by level, not by row.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)