Re: Data Sorting based on Logic Ties

[Steven Calvert <calvert@ULETH.CA>]

Another "interesting" one!

If I understand the problem, you want to start with the first step and progress
through the records. In each case, the "next" step depends on whether or not a
record exists with the "current step value" in the "depends_on" field. The
following pseudo-code outlines a solution. Note that you should start with the
lowest step which does not "depend on" another step (just in case step 1
depends on a higher step). The procedure also assumes that the data is clean
(no loops, ...):

  let curr_step = select min(step) from <table> where depends_on is null
  loop until done
    print curr_step
    let next_step = select step from <table> where depends_on = curr_step
    if "no_record_found"
      let next_step = select step from <table> where step = curr_step + 1 and
depends_on is null
      if "no_record_found"
        let done = true
      end if
    end if
    let curr_step = next_step
  end loop

Of course if you are using Oracle (do other databases have "connect by"?), the
following works and is a lot easier:

  select step
  from <table>
  connect by prior step = nvl(depends_on,step-1)
  start with step=
    (select min(step) from <table> where depends_on is null);

HTH.

John Walker wrote:

> I have the following data that I'm trying to sort in SQR 3.
>
> Step #         Depends on
> 1
> 2
> 3
> 4
> 5         9
> 6
> 7
> 8         4
> 9         8
>
> The desired end result is step 1, 2, 3, 4, 8, 9, 5, 6, 7.
>
> This is because the depends on field tells me that step 8 goes after step
> 4, step 9 goes after 8 and step 5 goes after 9.
>
> Does anyone have any suggestions about what kind of logic I could use?
> Many thanks in advance.

--
Steven Calvert
calvert@uleth.ca
University of Lethbridge
(403)329-2071