Union queries and sorts (1 Viewer)

[ryetee]

I have a union query which is formed from 2 other queries to join 2 similar tables into 1.
The first query takes the rows from table1 where a certain item in the table is equal to x.
It doesn't really matter what the 2nd query does other than it uses a different table, table2..

I join the 2 tables together and when presented to the user the rows from the 1st able are show first and the from the second table after. This is exactly what I want!

However I would like to sort the first table into some order and then apply the same to the second table.
I thought this would be as easy as putting an ORDER BY on each of the component select statements. This has the effect of sorting the entire union. If I only put the order by on the first select nothing is sorted and if I put it on the 2nd select the whole union is sorted.

Is there any way to have table 1 sorted by x followed by table 2 sorted by x?

 

[arnelgp]

add another dummy column to both:

select 1 as expr, table1.field1,....
union
select 2 as expr, table2.field1,...

 

[arnelgp]

add another dummy column to both:

select 1 as expr, table1.field1,.... order by expr,...
union
select 2 as expr, table2.field1,...
order by expr

 

[ryetee]

add another dummy column to both:

select 1 as expr, table1.field1,.... order by expr,...
union
select 2 as expr, table2.field1,...
order by expr

Not entirely sure what you mean here.

Btw I'm using UNION ALL
WHen I just use union the sort sorts both tables as if they were one

EDIT: OK not sure if this is what you meant but I've added the following to each of the 2 selects

"1 " & [table1.thesortorderiwant] AS SrtOrder
and
"2 " & [table2].[thesortorderiwant] AS srtOrder

and then sorted on srtOrder so I get all the records from table 1 in order followed by all records from table 2 in same order

 

[arnelgp]

that will do too, what i meant is this:

select 1 as expr1, [table1.thesortorderiwant] ...
union all
select 2 as expr1, [table2].[thesortorderiwant] ...