This is my first attempt at answering my own question, since someone may well run into this and so it might be of help. Using Firebird, I want to combine the results of two queries using UNION ALL, then sort the resulting output on a given column. Something like:
(select C1, C2, C3 from T1)
union all
(select C1, C2, C3 from T2)
order by C3
The parentheses came from valid syntax for other databases, and are needed to make sure the arguments to UNION ALL (an operation that's defined to work on tables - i.e. an unordered set of records) don't try to be ordered individually. However I couldn't get this syntax to work in Firebird - how can it be done?
The UNION ALL operator can use the ORDER BY clause to order the results of the query in SQL Server (Transact-SQL).
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
SQL Server will give error if ORDER BY is used inside a subquery of a UNION operator. MySQL will ignore the ORDER BY clause inside a subquery of a UNION operator.
SELECT C1, C2, C3
FROM (
select C1, C2, C3 from T1
union all
select C1, C2, C3 from T2
)
order by C3
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With