mohamed shubber mohamed shubber - 6 months ago 13
SQL Question

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

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???and thanks in advance.

Answer

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;
Comments