access-SQL-Query - Using "Order By" in UNION ALL

Question

I tried this SQL query in Microsoft access 2016

SELECT * FROM (
SELECT table1.name , table1.age FROM table1 ORDER BY table1.age 
)
union all

SELECT * FROM (
SELECT table2.name , table2.age FROM table2 ORDER BY table2.age
)
union all

SELECT * FROM (
SELECT table3.name , table3.age FROM table3 ORDER BY table3.age
);

that I found in a similar question but it didn't work for me, this is my result:

name    age
aa      100
bb      66
cc      200
dd      78
tt      38
gg      77

which is the same order my tables is,and the result i want is to be like this :-

name    age
bb      66
aa      100
dd      78
cc      200
tt      38
gg      77

Where did I do wrong?

Richard Hansell · Accepted Answer · 2016-05-06T10:00:32.383

2

Try this:

SELECT table1.name, table1.age FROM table1
UNION ALL
SELECT table2.name, table2.age FROM table2
UNION ALL
SELECT table3.name, table3.age FROM table3 
ORDER BY 2;

You are ordering the results from your sub-queries, then joining these together into an unordered list. You need to move the ORDER BY to the end of the query.

As Damien says, you need to ORDER BY at the end of your query, otherwise you can't guarantee the results will always be what you want. Something like this should do the job:

SELECT name, age FROM (
    SELECT 1 AS table_order, table1.name, table1.age FROM table1
    UNION ALL
    SELECT 2 AS table_order, table2.name, table2.age FROM table2
    UNION ALL
    SELECT 3 AS table_order, table3.name, table3.age FROM table3 
) x
ORDER BY table_order, age;

edited May 06 '16 at 10:00

answered May 06 '16 at 09:32

Richard Hansell

5,315
1
16
35

am sorry,i just edited my question to explain why i used this code,i don't want them all to be grouped in one order – mohamed shubber May 06 '16 at 09:36
@mohamedshubber - you *have* to order the entire result set if you want to guarantee the overall order of results. You may want to expose an additional column (e.g. `table1.age, 1 as tableNo` in the first select, and so on) so that you can specify the order you want. There is *no* guarantee that, with a `UNION` (`ALL`) that the rows produced by the top query will appear before the rows from the second query. – Damien_The_Unbeliever May 06 '16 at 09:44
thank you,it worked like a charm,i was googling for what u suggested but now you saved me some time,thanks again – mohamed shubber May 06 '16 at 10:13

score 1 · Answer 2 · answered May 06 '16 at 09:32

1

Dont use select * from on top of select. Use like below

SELECT table1.name , table1.age FROM table1 ORDER BY table1.age 
union all
SELECT table2.name , table2.age FROM table2 ORDER BY table2.age
union all
SELECT table3.name , table3.age FROM table3 ORDER BY table3.age

answered May 06 '16 at 09:32

Utsav

7,914
2
17
38

i got this error : The ORDER BY expression (table2.age) includes fields that are not selected by the query. Only those fields requested in the first query can be included in an ORDER BY expression. – mohamed shubber May 06 '16 at 09:42
are you using the query as it is or changing it for your tables. If yes then make sure to select same columns in select for all tables – Utsav May 06 '16 at 09:47

access-SQL-Query - Using "Order By" in UNION ALL

2 Answers2