Combining UNION ALL and ORDER BY in Firebird

Question

Using Firebird, I want to combine the results of two queries using UNION ALL, then sort the resulting output on a given column.

(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?

score 32 · Accepted Answer · answered Dec 09 '08 at 21:08

32

SELECT C1, C2, C3
FROM (
    select C1, C2, C3 from T1
    union all 
    select C1, C2, C3 from T2
)
order by C3

answered Dec 09 '08 at 21:08

Cade Roux

88,164
40
182
265

Note that this query requires Firebird 2.x or higher – alldayremix Jun 15 '16 at 16:03

score 13 · Answer 2 · answered Dec 09 '08 at 22:18

13

Field names are not required to be equal. That's why you can't use the field name in the order by.
You may use the field index instead. As in:

(select C1, C2, C3 from T1)
union all 
(select C7, C8, C9 from T2)
order by 3

answered Dec 09 '08 at 22:18

Douglas Tosi

2,310
2
18
18

As written this requires Firebird 2.x, but if you remove the parentheses this will also work in Firebird 1.5 – alldayremix Jun 15 '16 at 16:02

score 7 · Answer 3 · edited Jun 23 '15 at 16:03

7

How about:

select C1, C2, C3 from T1
union all 
select C1, C2, C3 from T2
order by 2

At least in the newer Firebird Versions it works if you order by "Number" instead of using an Alias.

edited Jun 23 '15 at 16:03

josliber

43,891
12
98
133

answered Jun 23 '15 at 15:15

KCM

71
1
1

score 2 · Answer 4 · answered Feb 03 '09 at 14:48

2

In Firebird 1.5 this works for me

create view V1 (C1, C2, C3) as
  select C1, C2, C3 from T1
  union all 
  select C1, C2, C3 from T2

and then

select C1, C2, C3 from V1 order by C3

answered Feb 03 '09 at 14:48

Tiago Moraes

318
3
7

+1 for "backwards" compatibility, the other answers doesn't work in Firebird 1.5 – Eliseo Ocampos Jul 17 '09 at 18:35
@Chris This is the same answer as Chris' one. You should delete yours or improve Chris' answer ;) – bluish Dec 16 '14 at 10:07

score 1 · Answer 5 · answered Dec 09 '08 at 21:02

1

Perform the UNION ALL in a view (without the ORDER BY clause), then select from the view using ORDER BY.

answered Dec 09 '08 at 21:02

Chris

1,400
1
10
14

+1 for "backwards" compatibility, the other answers doesn't work in Firebird 1.5 – Eliseo Ocampos Jul 17 '09 at 18:34

score 0 · Answer 6 · answered Apr 17 '17 at 21:59

0

Moving order by into a query tail has no effect to output datagrid.

select * from (
    select first 1
        C1
    from T1
    order by id desc
)
union all
select * from (
    select first 1
        C1
    from T2
    order by id desc
)

answered Apr 17 '17 at 21:59

kai3341

89
7

score 0 · Answer 7 · answered Dec 01 '22 at 16:59

It's more verbose and complicated than other solutions, but perhaps you still find this useful. You can use NATURAL FULL JOIN with a distinct discriminator between its operands to produce UNION ALL behaviour. With that, you can continue ordering by column names, or other expressions:

select c1, c2, c3 
from (select 't1' as x, t1.* from t1) as t1
natural full join (select 't2' as x, t2.* from t2) as t2
order by c3

Combining UNION ALL and ORDER BY in Firebird

7 Answers7

Linked