|Subject||Re: [firebird-support] SQL puzzle: Order based on Prerequisite|
In a table called "Steps", I have the following fields:
ID, B4Me, Dsc,
-ID field contains a unique ID for each record
-B4Me contains the ID of some other record in the table that MUST
appear in a result set, BEFORE this record. B4Me may be null. This is called the
-Records will be entered in random order, so Natural order cannot be
relied upon as a substitute for B4Me
I need an SQL statement that will return all records with a user
supplied search word (such as "oil", or "glycol" or "micron") in the Dsc field,
which could return a variable number of records, but what ever the number of
records returned, the *order* must be such that each record is preceded by the
record who's ID matches the B4Me value of a given record.&nb sp;
- The SQL may return one or more records with a blank B4Me. In which case, Natural order can be used for those records, and they are all considered "First" or rather, "Before everything else". Once all the records with a blank B4Me are listed, the remaining must be in the B4Me order.
- It is possible, though unlikely, that following the B4Me order will result in a circular reference. (Two or more records that reference each other, or the last record in a large result set, references the first (or any other in the result set) record in the B4Me field). In these cases, the SQL must either list all records until a circular reference is detected, then don't list the "offending" record, or display an error message, but it must not hang FB and get an "out of memory" error./ p>
Thanks in advance for any help you can provide.