>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 "B4Me" order.
>-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.&nbs p;
>
>Important Notes:
>
>- 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.
>
>Thanks in advance for any help you can provide.

The possibility of a circular reference, makes this a difficult task. SQL is great for working with sets of data, iteration and recursion are more difficult, but doable with CTEs or stored procedures, but I cannot think of how to exit a circular reference "gracefully" within one statement.

Hence, I'd recommend using a global temporary table:

CREATE GLOBAL TEMPORARY TABLE MyTmp
(ID INTEGER, OrderNo INTEGER)
ON COMMIT DELETE ROWS; --doesn't really matter whether you commit or delete rows in this particular case...

and then EXECUTE BLOCK or a STORED PROCEDURE;

EXECUTE BLOCK(MySearch varchar(50) = :MySearch) Returns(OutID Integer) as
declare variable ID integer;
declare variable ID2 integer;
declare variable OrderNo integer;
begin
delete from MyTmp; --start afresh every time you execute this block
OrderNo = 1000;
for select ID, B4Me
from "Steps"
where Dsc containing :MySearch
into :ID, :ID2 do
begin
while (:ID is not null) do
begin
if exists(select * from MyTmp where ID = :ID) then
begin --This is how to escape from a circular reference
id = null;
end
else
begin
INSERT INTO MyTmp(ID, OrderNo)
VALUES(:ID, :OrderNo);
OrderNo = OrderNo - 1;
ID = ID2;
if (ID is not null) then
begin
SELECT B4Me from "Steps" where ID = :ID into :ID2;
end
end
end
end
for select ID from MyTmp order by OrderNo into :OutID do
suspend;
end

I've interpreted "Before everything else" to mean "before any of its own children", not "before any children regardless of heritage" - or, to put it simple - you would appear before all your children, but not necessarily before my children.

Hopefully, this can serve as a template for how you can solve your situation, despite being more than one statement.
Set