Hi, or better for “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”
select CASE WHEN B4ME IS NULL THEN 0 ELSE 1 END, CASE WHEN B4ME IS NULL THEN ID ELSE B4ME+1 END, other_fields FROM STEPS ORDER BY 1 ASC, 2 ASC regards, Karol Bieniaszewski From: mailto:[email protected] Sent: Saturday, April 11, 2015 10:03 AM To: [email protected] Subject: Re: [firebird-support] SQL puzzle: Order based on Prerequisite Hi, you must rethink what you try to retrive. What is then sense of returning somethink in case of cycles? If you have cycles then you do not get something before.. But if you need something strange then it is simple ID as integer select CASE WHEN B4ME IS NULL THEN ID ELSE B4ME+1 END, other_fields FROM STEPS ORDER BY 1 ASC but with cycles i will prefere recursive cte or stored proc and make possibility of duplicates in resultset regards, Karol Bieniaszewski From: mailto:[email protected] Sent: Wednesday, April 08, 2015 4:28 PM To: [email protected] Subject: [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 "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.&nb sp; 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./ p> Thanks in advance for any help you can provide.
