Re: SQL help requested - "linked list"

"Mia" <nospam_at_cox.net> wrote in message news:3F454D25.7050707_at_cox.net...
> Thanks again Bob,
>
> I'm using Oracle.

As I recall, the Oracle syntax for expressing recursion uses a CONNECT BY clause.

> I don't actually care about the order. I just want to get the IDs.

Au contraire. If you want to get the IDs that come before or to get the IDs that come after a particular ID, you care only about order.

What, if not implicit order, does the juxtaposition of ID and NEXTID provide?

One must ask: What does this implicit order mean? How does it arise in the first place? If a human puts the rows into some order, upon what information does the human base the decisions? Can one represent this information directly?

An ID/NEXTID relation might be the best possible way to model this data, and one must know all of the business requirements to decide. I think the decision is sufficiently suspect, however, to justify additional scrutiny. I would definitely consider it a decision of last resort.

I am basing these comments on your description of the data as a linked list. I assume from this that each ID has at most one immediate successor and at most one immediate predecessor. I also assume cycles are prohibited. The constraints for unique immediate predecessors and successors are easy. How are you going to declare the constraint prohibiting cycles? If you do not prohibit cycles, how do you expect your proposed queries to react in the presence of cycles?

> I'm thinking I might want to know which IDs occur pervious versus which
> ones occur after, but that's not a requirement in my case.

Isn't that exactly the question you started with? Am I missing something?

> -Mia
>
>
> Bob Badour wrote:
> > "Mia" <nospam_at_cox.net> wrote in message news:3F4540C5.4050201_at_cox.net...
> >
> >>Thanks Bob,
> >>
> >>In my mind I was thinking one thing but I did not state it very well. I
> >>want to select not just the immediate previous and next IDs, but all the
> >>ID's - the previous to the previous and the next of the next, and so on,
> >>all the way to the ends of the list.
> >
> >
> > In that case, the answer will depend on the dbms you are using. In
general,
> > SQL does not support recursive queries; although, many products do.
> >
> > I assume you cannot guarantee (nextid > id) for all rows in each table.
I
> > suggest you consider a solution that directly models order using ordered
> > values instead of recreating physical order logically.
> >
> >
>
Received on Fri Aug 22 2003 - 22:42:10 CEST