Solved OrderBy in Union Query (1 Viewer)

[KitaYama]

This is a simple union query I'm using:

SELECT UsedProductFK
FROM tblUsedMaterials
WHERE UsedProductFK=47906

UNION

SELECT SharedProductFK
FROM tblUsedMaterials_Shared ;

this is what the result look like :

I need the query show the result of tblUsedPaterials first, and then the result from tblUsedMaterials_Shared.

The result should be :
47906 ---> from tblUsedMaterials
47918 ---> from tblUsedMaterials_shared (first record)
18870 ---> from tblUsedMaterials_shared (Second record)

It seems the query is sorting by UsedProductFK even if no ORDER BY is added to the sql.

Thank you.

 

[theDBguy]

Would something like this work?

SELECT UsedProductFK, 1 As Sort
FROM...
WHERE...
UNION 
SELECT SharedProductFK, 0
FROM...
ORDER BY Sort

(untested)

 

[arnelgp]

SELECT UsedProductFK FROM (SELECT 1, UsedProductFK
FROM tblUsedMaterials
WHERE UsedProductFK=47906

UNION

SELECT 2, SharedProductFK
FROM tblUsedMaterials_Shared) ;

 

[KitaYama]

Thanks for trying, but still no. The problem is partialy solved, but still the result is not what I expect.

The result of tblUsedMaterial is the first one. This is OK.
But it seems that the result of second table is DESC. The last record is shown as first.

The result should be :
47906 ---> from tblUsedMaterials
47918 ---> from tblUsedMaterials_shared (first record)
18870 ---> from tblUsedMaterials_shared (Second record)

 

[arnelgp]

SELECT UsedProductFK FROM (SELECT 1 As X, UsedProductFK
FROM tblUsedMaterials
WHERE UsedProductFK=47906

UNION

SELECT 2, SharedProductFK
FROM tblUsedMaterials_Shared)

ORDER BY X ASC, UsedProductFK DESC;

 

[plog]

Now you need 3 fields in each SELECT inside the UNION:

SELECT ProductID
FROM
(SELECT UsedProductFK AS ProductID, 1 AS SourceOrder, 1 AS RecordOrder
FROM tblUsedMaterials
WHERE UsedProductFK=47906

UNION

SELECT SharedProductFK AS ProductID, 2 AS SourceOrder, SharedUsedMaterialsPK AS RecordOrder
FROM tblUsedMaterials_Shared)
)
ORDER BY SourceOrder, RecordOrder

 

[KitaYama]

Would something like this work?

SELECT UsedProductFK, 1 As Sort
FROM...
WHERE...
UNION
SELECT SharedProductFK, 0
FROM...
ORDER BY Sort

(untested)

@theDBguy I'm sorry. For no reasons, I missed your post. Thanks for trying to help.
I'm receiving the following error on your try.

EDIT : I had a mistake in the suggested syntax.
Correcting the misstake, I receive no error, but still the sort is not correct.
My Appologies.

 

[KitaYama]

Now you need 3 fields in each SELECT inside the UNION:

SELECT ProductID
FROM
(SELECT UsedProductFK AS ProductID, 1 AS SourceOrder, 1 AS RecordOrder
FROM tblUsedMaterials
WHERE UsedProductFK=47906

UNION

SELECT SharedProductFK AS ProductID, 2 AS SourceOrder, SharedUsedMaterialsPK AS RecordOrder
FROM tblUsedMaterials_Shared)
)
ORDER BY SourceOrder, RecordOrder

@plog, Your try gave me a syntax error. I removed the last ) and it's giving me the result I expect.
Thanks.

I had no idea a very simple union has such a fuss on showing the order.
To me, it's more logical that a union without ORDER BY, showing the result of table 1 + table 2 based on table PKs rather than sorting the result by the fields it's showing (even if no order by is mentioned)

Thanks again.

 

[KitaYama]

SELECT UsedProductFK FROM (SELECT 1 As X, UsedProductFK
FROM tblUsedMaterials
WHERE UsedProductFK=47906

UNION

SELECT 2, SharedProductFK
FROM tblUsedMaterials_Shared)

ORDER BY X ASC, UsedProductFK DESC;

@arnelgp Perfect.
Now it is what I expect.

 

[KitaYama]

Both @arnelgp and @plog 's methods are showing what I expect.
Since it was only a simplified version of what I actually am using and the union query is structured in vba and based on a lot of other conditions, the shorter the sql, the simpler to work on the conditions and its structure.

For now I use @arnelgp's solution (solely because of being shorter), and I mark the thread as solved because now I have two working methods.

Still shorter possibilities are welcomed.

Thanks to all who tried to help.

 

[theDBguy]

@theDBguy I'm sorry. For no reasons, I missed your post. Thanks for trying to help.
I'm receiving the following error on your try.

View attachment 108022

That's okay, but it looks like you may have typed it differently than what I posted. Cheers!

 

[KitaYama]

That's okay, but it looks like you may have typed it differently than what I posted. Cheers!

My appologies for the difference.
I corrected the syntax. No error this time. But still the sort is not correct.

Million thanks.

 

[The_Doc_Man]

In searching for the solution to this problem, I found two variant methods.

1. The ORDER BY clause can ONLY be applied to the last member-query of the set of queries in the UNION.

SELECT A, B, C FROM X 
UNION
SELECT A, B, C FROM Y
ORDER BY B

2. Make the entire UNION query into a sub-query that is encapsulated by something that performs the sort on the sub-query's result set.

SELECT A, B, C FROM
(
    SELECT A, B, C FROM X
    UNION
    SELECT A, B, C FROM Y 
)
ORDER BY B ;

 

[Galaxiom]

The ORDER BY clause can ONLY be applied to the last member-query of the set of queries in the UNION.

I believe your source has misunderstood. The order by applied to the whole, not "the last member-query of the set of queries in the union".

UNION precedes ORDER BY in order of execution, so the ORDER BY is applied to the already unioned data. A subquery is not necessary.

 

[KitaYama]

1. The ORDER BY clause can ONLY be applied to the last member-query of the set of queries in the UNION.

I doubt if it's correct. (If I understand what you mean.)
I changed the code from my first post to this:

SELECT UsedProductFK
FROM tblUsedMaterials
WHERE UsedProductFK=47906

UNION

SELECT SharedProductFK
FROM tblUsedMaterials_Shared
Order BY SharedProductFK ;

As you see SharedProductFK is from last select statement.
But running this query, throws this error message.

As you see, it explicitly says Access needs the ORDER BY from the first Select.

2. Make the entire UNION query into a sub-query that is encapsulated by something that performs the sort on the sub-query's result set.

I've read that using sub queries on large datasets has some effects on performance. Our tables have more than a million records.
Do you think we will face some performance issues?
Meanwhile I will test as soon as possible.

Thanks for taking your time and responding.

 

[KitaYama]

The order by applied to the whole, not "the last member-query of the set of queries in the union".
UNION precedes ORDER BY in order of execution, so the ORDER BY is applied to the already unioned data.

See #15
Isn't it First and not whole?

 

[The_Doc_Man]

I believe your source has misunderstood. The order by applied to the whole, not "the last member-query of the set of queries in the union".

UNION precedes ORDER BY in order of execution, so the ORDER BY is applied to the already unioned data. A subquery is not necessary.

For "after last member of the set of UNIONs" or "with the last member of the set of UNIONS"

SQL SERVER - UNION ALL and ORDER BY - How to Order Table Separately While Using UNION ALL - SQL Authority with Pinal Dave

I often see developers trying following syntax while using ORDER BY. SELECT Columns FROM TABLE1 ORDER BY Columns UNION ALL SELECT Columns FROM TABLE2

blog.sqlauthority.com

Guru: SQL Facts Of UNION And ORDER BY - IT Jungle

UNION and ORDER BY are powerful SQL features, but put the two together and you may get some strange and frustrating error messages. Fortunately for us DB2 for i professionals, there are easy ways to make the two collaborate and cooperate. Today is a great day to be sure we understand them. We...

www.itjungle.com

SQL Server FAQ - Using ORDER BY with UNION Operators

How to use order by in union query in SQL? - Intellipaat Community

Can anyone explain how to use order by in union query in SQL?

intellipaat.com

However, the implication is often that (at least for SQL Server) you parenthetically isolate the queries from the UNION and from the ORDER BY. See the 3rd link for that case.

You would be surprised at how many articles say that the ORDER BY must follow the last member of the set of queries - but many of them do not emphasize the parenthetical isolation of the queries. It seems to be vendor dependent as to whether you need the isolation.

 

[The_Doc_Man]

As you see, it explicitly says Access needs the ORDER BY from the first Select.

Actually, that's not what it said. It said you didn't use the field name from the first SELECT. That is because in a UNION query, the field names are defined by the first SELECT portion. The name you used that tripped the error used the field name from the later UNION member - but that name is actually meaningless outside of that 2nd member's specific context.

 

[KitaYama]

Actually, that's not what it said. It said you didn't use the field name from the first SELECT. That is because in a UNION query, the field names are defined by the first SELECT portion. The name you used that tripped the error used the field name from the later UNION member - but that name is actually meaningless outside of that 2nd member's specific context.

thanks for the clarification.

 

[isladogs]

To reiterate, the field names are defined by the first section of the UNION but the ORDER BY is done at the end.
If the field name used for ORDER BY isn't in the first section, use an ALIAS for that field name.