Combining ORDER BY and UNION

Without a transformation, a statement that contains both ORDER BY and UNION would require two separate sorting steps-one to satisfy ORDER BY and one to satisfy UNION (Currently Derby uses sorting to eliminate duplicates from a UNION. You can use UNION ALL to avoid sorting, but UNION ALL will return duplicates. So you only use UNION ALL to avoid sorting if you know that there are no duplicate rows in the tables).

In some situations, Derby can transform the statement internally into one that contains only one of these keywords (the ORDER BY is thrown out). The requirements are:
Derby will be able to transform the following statements:
SELECT miles, meal
FROM Flights
UNION VALUES (1000, 'D')
ORDER BY 1
Derby cannot avoid two sorting nodes in the following statement, because of the order of the columns in the ORDER BY clause:
SELECT flight_id, segment_number FROM Flights
UNION
SELECT flight_id, segment_number FROM FlightAvailability
ORDER BY segment_number , flight_id
Related concepts
DISTINCT elimination based on a uniqueness condition
Combining ORDER BY and DISTINCT