Union all to multiple queries - how do you order them ? (1 Viewer)

[johnseito]

I did union all to many queries but how do I order them as which ones goes in 1, 2, 3rd etc. ? It doesn't seem like the first query in the union all is the first one shown, and the second query in the union all is the second one shown and so on etc. How do I order it in a way that the first one is the first one show, the second one is the second one shown, so on etc.

If this is not possible is there a way we can add another extra field and just tag the first query as 1, second query as 2 and so on and then sort them in ascending order ?

 

[Minty]

You can't determine the order of records in a Union query in that way (and shouldn't rely on it). Normally you would simply create a query on the Union query and apply a sort order to that.

 

[arnelgp]

add another Expression in your Union for Grouping purpose:
example:

Select Field1, Field2, Field3, 1 As Expr1 From Query1
Union
Select Field1, Field2, Field3, 2 As Expr1 From Query2
Union
Select Field1, Field2, Field3, 3 As Expr1 From Query3 Order By 4


The 1, 2, 3 there are the extra Expression you add.
then you can order them.
the 4 there means sort on the 4 field (Expr1)

 

[Galaxiom]

add another Expression in your Union for Grouping purpose:
example:

Select Field1, Field2, Field3, 1 As Expr1 From Query1
Union
Select Field1, Field2, Field3, 2 As Expr1 From Query2
Union
Select Field1, Field2, Field3, 3 As Expr1 From Query3 Order By 4


The 1, 2, 3 there are the extra Expression you add.
then you can order them.
the 4 there means sort on the 4 field (Expr1)

Using UNION keyword will waste a lot of time looking for duplicate records. Use UNION ALL to get all the results. There won't be duplicates because of the added field anyway.

There is no point assigning a name to the Expr1 field in the second and subsequent subqueries since the names are defined entirely by the first subquery.

I recommend against the practice of using the field number in any ORDER BY. It is easily overlooked if another field is subsequently added. The tiny bit of typing it saves just isn't justified and it isn't good for readability.

 

[arnelgp]

On my example no need to add ALL keyword since there is another unique value (the added Expr).

 

[Galaxiom]

On my example no need to add ALL keyword since there is another unique value (the added Expr).

Yes, there is no "need" for ALL and the results will be the same either way. However without it, the engine will put a lot of pointless work into checking for duplicates. The ALL keyword suppresses that testing.

UNION ALL simply appends the subqueries without bothering to analyse the records and is much faster.

 

[johnseito]

You can't determine the order of records in a Union query in that way (and shouldn't rely on it). Normally you would simply create a query on the Union query and apply a sort order to that.

I did just this but my ORDER by clause for the outer query to the two union query didn't work. I did first field order by as ascending and second field as descending, my result is second field is still in ascending order.

Second field is a field I added - union of the two queries, with the first as 1 and second as 2.

 

[Minty]

Pull your saved union query into a new query design window. Now query that and apply a sort.