Subject Re: [firebird-support] SQL puzzle: Order based on Prerequisite
Author
Post date 2015-04-11T08:07:45Z
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
 
Sent: Saturday, April 11, 2015 10:03 AM
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
 
 
 
Sent: Wednesday, April 08, 2015 4:28 PM
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.