Solved OrderBy in Union Query

[Josef P.]

Additional: it is also possible to specify only the data field number.

SELECT 1 as X, UsedProductFK
FROM tblUsedMaterials

UsedProductFK=47906

UNION

SELECT 2 as X, SharedProductFK
FROM tblUsedMaterials_Shared


Order BY 1, 2

 

[KitaYama]

Additional: it is also possible to specify only the data field number.

SELECT 1 as X, UsedProductFK
FROM tblUsedMaterials

UsedProductFK=47906

UNION

SELECT 2 as X, SharedProductFK
FROM tblUsedMaterials_Shared


Order BY 1, 2

@Josef P.
Unfortunately yours has the same problem.
The sort is not correct.
I had to changed your code as following to make it show what was asked.
Order BY 1 ASC, 2 DESC

I think it's because Access sorts the result based on the first field in the query. So I have to add ASC,DESC to correct it.

But for someone like me who is not even able to sort a union query, your solution is much easier to understand.
Much appreciated. After the correction, I think I have a better understanding than half an hour ago.

Thanks again.

Just a note for those who may refer to #21 in future : WHERE keyword should be added

 

[CJ_London]

I've read that using sub queries on large datasets has some effects on performance. Our tables have more than a million records.

Here you are using a union query and if you alias it, it is still not a sub query. However union queries can be inefficient for sorting/filtering/subsequent criteria as the indexing is not available. So better to include criteria and ordering in each union part.

The order of each union part can also make a difference - data from later selects appear at the top of the resulting dataset, not the bottom as you would expect. Not sure why or wether always consistent but suspect it might have something to do with the generated query plan - might be the simpler the select, the earlier it is processed. So try swapping the two selects around, might save the need for a sort

also a simple union will remove duplicates so you might be better using union all,

a sub query is one that is processed on each row of the recordset which is why they can be slow. Not always possible but can look at using an aliased query instead of a sub query either using non standard joins or as a Cartesian query

 

[KitaYama]

The order of each union part can also make a difference - data from later selects appear at the top of the resulting dataset, not the bottom as you would expect.

I really don't mind it's the first or last. My main problem was it was mixed. and Access was sorting the result by the only visible field.
Check the image in my first post and you'll see that the second record is from first select, the first and third records are from second select.
Later, I was obsessed to put the order to certain one.

So better to include criteria and ordering in each union part.

Thanks for the advice. I already have it.

also a simple union will remove duplicates so you might be better using union all,

I need to hide duplicate. Hence used Union

Thanks for the info on subqueries and indexes in Union queries. I had no idea how they work.
Today I feel a little smarter than the man I was yesterday.

Thanks for the time you put on given advice.

 

[CJ_London]

I need to hide duplicate. Hence used Union

OK - it was mixed because of the union - implication is one of those first two records has a duplicate in the other table. The query would return whichever record it found first. By adding a 'source' value the record is not unique so you should get two records where before you added the 'source' field you only had one. However duplicates within the same source will still be removed.

Hope that makes sense!

Not tested but another option might be

SELECT DISTINCT UsedProductFK
FROM tblUsedMaterials
WHERE UsedProductFK=47906

UNION ALL

SELECT DISTINCT SharedProductFK
FROM tblUsedMaterials_Shared

 

[KitaYama]

OK - it was mixed because of the union - implication is one of those first two records has a duplicate in the other table. The query would return whichever record it found first. By adding a 'source' value the record is not unique so you should get two records where before you added the 'source' field you only had one. However duplicates within the same source will still be removed.

Hope that makes sense!

Not tested but another option might be

SELECT DISTINCT UsedProductFK
FROM tblUsedMaterials
WHERE UsedProductFK=47906

UNION ALL

SELECT DISTINCT SharedProductFK
FROM tblUsedMaterials_Shared

Thanks. I'm home now.
I'll test it in the morning.

Thanks for additional info.